String functions in Power Query

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:

1. The function name changes from FIND to Text.PositionOf
2. The parameters for the text we want to find and the text we want to search in get flipped around!
3. 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

Case sensitivity:

While Excel formulas are not case sensitive, Power Query formulas are.  If the Power Query formula signature says “Text.Start” then “TEXT.START” or “text.start” will NOT work for you.

Base 1 vs Base 0

Excel formulas are what we refer to as “Base 1”.  This means that you count starting at 1.  Power Query, on the other hand starts counting at 0, not 1.  The implications of this are that it is very easy to write your formula referring to a number that is out by 1.  To see the effects of this, check the section below on the alternate for the MID function equivalent.