http://www.excelguru.ca/blog/2014/08/20/5-very-useful-text-formulas-power-query-edition/
LEFT
To get the left x characters, we basically replace LEFT with Text.Start:| Syntax | Example | Result | |
| Excel | =LEFT(text,num_chars) | =LEFT(“Excel”,2) | Ex |
| Power Query | =Text.Start(text,num_chars) | =Text.Start(“Excel”,2) | Ex |
MID
This one gets a bit weird. First we replace MID with Text.Range. Okay, no problem there. But look at the results when we pass the same parameters:| Syntax | Example | Result | |
| Excel | =MID(text,start,num_chars) | =LEFT(“Excel”,2,2) | xc |
| Power Query | =Text.Range(text, start,num_chars) | =Text.Range(“Excel”,2,2) | ce |
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. Text.Length is what we need instead of LEN.| Syntax | Example | Result | |
| Excel | =LEN(text) | =LEN(“Excel”) | 5 |
| Power Query | =Text.Length(text) | =Text.Length(“Excel”) | 5 |
FIND
And finally we come to the FIND function. This one is again a bit confusing. We’ve got 3 things to consider here:- The function name changes from FIND to Text.PositionOf
- The parameters for the text we want to find and the text we want to search in get flipped around!
- The result is 0 based, not 1 based
| Syntax | Example | Result | |
| Excel | =FIND(find_text,within_text) | =FIND(“xc”,“Excel”) | 2 |
| Power Query | =Text.PositionOf(text, find_text) | =Text.PositionOf(“Excel”,”xc”) | 1 |
