Excel Text Function Equivalents

2015-06-03
Ken Puls
Categories: Excel, Power BI, Power Query

For some time it’s bothered me that we don’t have full parity between Excel and Power Query functions. In this post I’m going to look at four Excel text function equivalents, intended to allow an Excel user to just work with formulas as they’re used to.

=LEFT()

The first function is the LEFT function which, if implemented in M, would be:

(text as text,num_chars as number)=>
let
ReturnValue = Text.Start(text,num_chars)
in
ReturnValue

You’d call it like so:

=LEFT([Word],4)

And it would return the Excel text function equivalent as shown here. (Excel’s output is in teh LEFT(x,4) column, and the Power Query LEFT function is shown in the pqLeft column.)

This one may not be a big deal, as it’s just a new function name, but it now works exactly as an Excel user would expect. (Except for the fact that you MUST type the function name in uppercase.)

=RIGHT()

We can do the same thing with the RIGHT function:

(text as text,num_chars as number)=>
let
ReturnValue = Text.End(text,num_chars)
in
ReturnValue

And call it with:

=RIGHT([Word],4)

Returning:

=LEN()

This is yet another simple one:

(text as text)=>
let
ReturnValue = Text.Length(text)
in
ReturnValue

Called with:

=LEN([Word])

Returning:

Why I care about Excel text function equivalents?

Beyond allowing a user to just use the knowledge they’ve been investing in for years, it’s the next function that makes a difference. With the MID function we have a bunch of things that are different, and to replicate our expectations we need to do some manipulation and conditional testing.

=MID()

This is what it takes to replicate the MID() function in Power Query:

(text as text,start_num as number,num_chars as number)=>
let

tLength = Text.Length(text),
StartCorrected = if start_num>tLength then -1 else start_num-1,
ReturnCorrected = if (StartCorrected+num_chars) > tLength then tLength - start_num + 1 else num_chars,
ReturnValue = if StartCorrected >-1
then Text.Range(text,StartCorrected,ReturnCorrected)
else null
in
ReturnValue

That’s a fairly big pain to just knock up in a couple of seconds! But once done, it’s easy to use:

=MID([Word],5,4)

And it returns results consistent to Excel as well:

Final Thoughts

I really wish these functions were built under and consistent with Excel’s function names to begin with. While we can replicate their form and function, it takes a lot of work to create/debug and implement them in a power query solution. In addition, some of the functions are VERY difficult to built. But regardless, at least we can.

22 thoughts on “Excel Text Function Equivalents”

Pingback: Excel Roundup 20150608 « Contextures Blog
Dennis Wallentin on June 9, 2015 at 7:07 am said:

Bear in mind that Microsoft's focus is to towards SQL Server rather then Excel and that's why we have to live with the inconsistency.
Ken Puls on June 9, 2015 at 7:38 am said:

I think it's even a bit more disparate than that, Dennis. I've head that a lot of the key players came from a Visual Studio environment. That would explain some of the more .NET focused styles.
Bill Szysz on June 11, 2015 at 5:34 am said:

Hi Ken,
If you defined previous functions (LEFT, RIGHT, LEN) then you can use its, inside definition of MID.
MID = (text as any,start_num as number,num_chars as number)=>
let
textOk = Text.From(text),
ReturnValue = try if LEN(textOk)
let
textOk = Text.From(text),
ReturnValue = try if Text.Length(textOk) < start_num then null
else
Text.Start(Text.End(textOk, Text.Length(textOk)-start_num+1),num_chars) otherwise null
in
ReturnValue

Regards 🙂
Bill Szysz on June 11, 2015 at 5:41 am said:

o, no... seems that your WordPress cut some parts of code (big parts) :-((
So, code in my previous post is completely wrong :-((
Ken Puls on June 15, 2015 at 7:35 am said:

Concept is still there though. You can nest other power query functions within each other. 🙂
Imke Feldmann on July 31, 2015 at 10:15 pm said:

Hi there,
you could also use alternative methods to cater for the conditions that will result in a more "Excel-like" Syntax:

(text as text,start_num as number,num_chars as number)=>

let

Mid = try Text.Range(text, start_num-1, List.Min({num_chars, Text.Length(text)-(start_num-1)})) otherwise ""

in
Mid
Ken Puls on August 3, 2015 at 7:41 am said:

Yeah, Imke, I like that. Nice and compact. 🙂
Tibo on August 12, 2015 at 12:30 am said:

Hi There,

Thanks for these.

Any idea of an equivalent of the type() function?

I've used Value.Type ([ColumnName]) but it returns results as hyperlinks when I'd like the value to be displayed.

Thanks

Tibo
Ken Puls on August 12, 2015 at 2:18 pm said:

Hi Tibo,

You're correct, Value.Type just doesn't want to play nicely. I'm not sure what's up with that, but will ping Microsoft on it.
Anthony Newell on September 24, 2015 at 3:11 am said:

Another top post, let's hope parity IS achieved soon
Francisco on November 18, 2015 at 11:15 am said:

Hi to all, need pq function equivalent for exact() in excel, can you help me?, thanks
Ken Puls on November 19, 2015 at 8:49 am said:

I think I'd just use an IF formula, since Power Query is case sensitive: if [Column1]=[Column2] then "match" else "no match"
Dave Potter on April 19, 2016 at 10:30 pm said:

I have just used a combination of PositionOf and List functions in place of mid to find a string like "ROOM 104", "ROOM 1.1" or "ROOM 1" etc.

So basically find the position of ROOM + its length. Get a list of all the occurrences of spaces. Find the values in the list greater than the text string then use the first two of those in Text.Range as the start and end values.
Damien on August 8, 2016 at 5:41 am said:

In the excel FIND formula, you can add in the syntax a start position. In the M language text.positionof ... this is not possible?
Is there a workaround?
Ken Puls on August 17, 2016 at 9:16 am said:

Hi Damien,

The workaround would be to use Text.Range() to provide the substring you're looking for.
ed hansberry on August 25, 2017 at 10:20 am said:

What is the difference between Text.Range and Text.Middle? I've been using Text.Middle as the Excel equivalent to MID() until I saw your M is for Data Monkey book. They seem to do the same thing.
ed hansberry on August 25, 2017 at 10:41 am said:

Oh, just figured it out.
= Text.Middle("Text",0,8) will work. It returns Text.
= Text.Range("Text",0,8) will error out as there are only 4 chars.

So I can skip pages 150-152 of your book. 😉
Text.Middle() must have come out fairly recently.
Ken Puls on August 28, 2017 at 9:13 am said:

Yeah, that's a new one to me, Ed. Glad they have it now, and I'll have to remember that when we update the book.
alex on June 14, 2018 at 1:18 pm said:

Is there a similar function TEXT (excel) in POWERQUERY? I want to reacreate =Text(column,"0000") . Where column has numbers or text with this values 2,3,7,22,345, V021. The target of this is change the number into this format 0002,0003,0007,0022,0345,V021

I really appreciate your help
Ken Puls on June 15, 2018 at 10:35 am said:

Hi Alex. Not exactly, but you could do this with =Text.End("0000"&[Column],4)
Pingback: Power Query's Extract Text Feature