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)=>

lettLength = 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.

Pingback: Excel Roundup 20150608 « Contextures Blog

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.

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.

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 🙂

o, no... seems that your WordPress cut some parts of code (big parts) :-((

So, code in my previous post is completely wrong :-((

Concept is still there though. You can nest other power query functions within each other. 🙂

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

Yeah, Imke, I like that. Nice and compact. 🙂

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

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.

Another top post, let's hope parity IS achieved soon

Hi to all, need pq function equivalent for exact() in excel, can you help me?, thanks

I think I'd just use an IF formula, since Power Query is case sensitive: if [Column1]=[Column2] then "match" else "no match"

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.

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?

Hi Damien,

The workaround would be to use Text.Range() to provide the substring you're looking for.

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.

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.

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.

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

Hi Alex. Not exactly, but you could do this with =Text.End("0000"&[Column],4)