#Power query clean text pro
That would be consistent with what the Excel pro would expect due to their experience with the MID function. That would be good too, especially if the signature could be tweaked to require a one based parameter for the start character. Maybe one day the PQ team can give us duplicate handles into the same back end function so that we can write stuff like this IN ADDITION to what already exists (don’t deprecate, just give us alternate routes): to all of my functions, but I really wish the rest was similar to Excel. And then, when I do get it right I’m constantly getting tripped up by the base 0 base 1 conversion.Īs this is a tool aimed at Excel users, I am a bit disappointed in the formula naming convention. Part of this is due to the fact that there is no auto-complete/syntax help in the Power Query engine (I’m sure it will come eventually), but part is also that my instinct is to type the Excel function name first. I find that even after using Power Query for a while now, I still have to look up the formula names from the Power Query formula categories page, both to find the formula name, and also the syntax. In Power Query it is too, but because it starts counting at 0 we get a 1. And look at that result… in Excel the x is treated as the 2nd character. But in the Power Query version, it’s completely opposite. So in the case of FIND, we put the “xc” first, and “Excel” second. The parameters for the text we want to find and the text we want to search in get flipped around!.The function name changes from FIND to Text.PositionOf.FINDĪnd finally we come to the FIND function. So this plainly works as a 1 based result in both Excel and Power Query. Notice that the result for this does return five characters, as you’d expect. Text.Length is what we need instead of LEN. Getting the length of a text string in Power Query is actually a bit more intuitive than Excel’s native function, only because the function name isn’t trimmed off. Interestingly though, the last parameter needs to be 2 to pull back 2 characters. So in this case, when we tell Power Query to start returning text at character 2, it pulls back c (E is 0, x is 1, c is 2). Where Excel’s formula language counts the word with E being character 1, Power Query considers that character 0.
They differ a little, don’t they? The issue comes down to that base 0 vs base 1 thing I mentioned above. But look at the results when we pass the same parameters: Okay, so we’re getting the hang of this now… Just change the function name and the rest work the same, right? Um, no. The function name needs to change from RIGHT to Text.End: To get the right x characters we have a similar situation.
To get the left x characters, we basically replace LEFT with Text.Start:Įasy enough once you recognize it, although I would have preferred that the formula name was consistent. Let’s take a look at how the five functions I illustrated in that original example differ from Excel to Power Query… LEFT To see the effects of this, check the section below on the alternate for the MID function equivalent. The implications of this are that it is very easy to write your formula referring to a number that is out by 1.
Power Query, on the other hand starts counting at 0, not 1. Base 1 vs Base 0Įxcel formulas are what we refer to as “Base 1”. If the Power Query formula signature says “Text.Start” then “TEXT.START” or “text.start” will NOT work for you. While Excel formulas are not case sensitive, Power Query formulas are. I personally think this is a bit of a mistake, and that the formula names in Power Query should have been a bit more congruent with standard Excel formulas (Power Pivot’s DAX functions are similarly named to Excel, so why not Power Query’s M language?) Some critical backgroundīefore you bang your head against the wall, there are two things that are really different between Excel formulas and Power Query formulas: Case sensitivity: Now, over the past few months I’ve been working with Power Query, and one of the things that’s been driving me a little crazy is that the formula names in Power Query and not the same as they are in Excel.
It even lets you try them out live in the web page without even having to open Excel at all. The article is geared to explaining five functions specific to working with Text in Excel, and are a set of the most under-utilized functions in Excel (in my opinion). I bring you 5 Very Useful Text Formulas - Power Query Edition. This post is alternative version, but with a twist. Years ago I published an article on my site called Five Very Useful Functions For Working With Text.
0 kommentar(er)
