What Power Query Functions Exist?

I know that this topic has been covered before by others, but I think it’s still pretty valuable for a user to be able to figure out what Power Query functions exist, especially since they are often different than what we’re used to in Excel.

NOTE:  This article was updated 2015-05-20 at the request of a reader to include more coverage on implementing the discovered function into the solution.

 

Power Query Functions Documentation on the web

There’s a pretty good resource site available on the Microsoft Support site.  Personally I have that one bookmarked and head over there often when I’m looking for a new function to do something.  I find that with a quick CTRL + F on the page, I can quickly search and narrow in on the function I think I need in order to learn it’s syntax.

To be fair, I’m not always in love with the actual examples (many lack a power query UI view), but overall the site is fairly useful.

Power Query Functions Documentation in the client

Now that’s all good, but what if you’re working on a plane with no WIFI, and you need to figure out the syntax for a new function?

As luck has it, there is a way to pull up the list for most functions right in the client.  To do this, I:

  • Clicked Add Column –> Add Custom Column
  • Typed a 1 and clicked OK
  • Went to the Power Query formula bar and typed the formula below.  (Notice that this is case sensitive)

 =#shared

(Why the custom column? Because typing in the formula bar replaces the previous step, and I want to be able to revert to that since it’s part of my logic:

image

Now, you’ll see you get a list of (almost) all the functions that you can access:

SNAGHTML894ffc53

Now, let’s assume I’m trying to find a formula to remove certain characters from a text string.  I really need to search for “Text.”, but there isn’t a search option.  No big deal, let’s convert this list into a table:

SNAGHTML89522a10

Once we’ve done that, we get a nice table of all of the functions, and we can filter them to our heart’s content.  Here’s my table filtered down to just rows that begin with “Text”:

SNAGHTML897b95c3

And a page or so down, I found something that looks like it might work:  Text.Remove.

Investigating the Function Syntax

I clicked on the green Function beside the Text.Remove entry.  It pops up an Invoke Function box, and behind that is the syntax for how it’s supposed to work.  So that’s pretty cool.  I tried it out with some text, as shown below:

txtremove1

Clicking OK returned the following:

txtremove2

Now this is a bit… weird… and frustrating. Value? Why Value? (I actually don’t know why, you’d think it would have been the function name, wouldn’t you?)

I stepped back to the Value step of the query, as I wanted to look at the syntax page that popped up behind the Invoke Function dialog:

image_thumb.png

 

My only complaint here is that once you land in this window, the only indicator of the actual function name is in the smallest font on the page, buried in the middle. You’d think that the name would should up a little more prominently. Regardless, I copied the name of the function, then stepped back to the Invoked FunctionValue step and replace Value in the formula bar with the function name:

txtremove3

Perfect, it works.

Implementing the Function in the Solution

Now let’s see if I can get it into my original query. To do that I:

  • Copied that entire line of M from the formula bar,
  • Selected the Source step (I wouldn’t be able to do this if I had typed #shared while I had the Source step selected originally),
  • Choose to Add New Column –> Add Custom Column –> Accept the inserted step,
  • Pasted the copied M into the formula area, and
  • Replaced the original text (“My –Dog –Has –Fleas”) with the name of the appropriate column from my data set.

Visually, it looks like this:

txtremove4

 

And then I checked the query to see that it worked:

txtremove5

 

Cleanup

Now that I’ve been able to explore the functions and found and implemented the one I’d like to use, I can just knock off the extra steps shown below in yellow, returning me back to my next step:

txtremove6

 

 

 

Learning more about Power Query functions

For reference, this is one of the many things that Miguel and I will be covering in our upcoming Power Query training workshops.  Learn more about the workshop and register here:  http://powerquery.training/course/

10 thoughts on “What Power Query Functions Exist?

  1. Ken,

    Thank you for the post.

    Could you please expand on your example and show how "Text.Remove" would appear in the finished query. Including a workbook with the complete example would be most appreciated.

  2. Another quality post Ken. I'll be bookmarking this along with your other info as you are becoming by go to reference along with Chris!
    I think there is a gap in the market for more Power Query reference books in the same vein as produced by Rob Collie / Bill Jelen with their Power Pivot books. Practical real life scenarios where you can make Power Query flex its various muscles
    Did you attempt importing data from a worksheet within an external workbook that has code in it?
    The behaviour when extracting data is very different and I'm having to convert that source workbook to a .xlsx with code disabed in meantime

  3. Thanks Anthony.

    On the book, we're working on it. Our intent is to use real life examples in M is for Data Monkey.

    On the other, unfortunately I haven't had the chance to mess around with it yet. Been too busy with a couple of other projects.

  4. @Anthony - interesting that you have that problem. I have had no problem with.xlsm files (although I don' have any with Workbook_Open or similar type code).
    On the other hand, I have had significant problems reading data from .xlsb files using Power Query. This has caused me to develop alternate strategies, like caching data from the .xlsb files in .xlsx extract files, and then reading multiple .xlsx files with append functions in Power Query.

  5. Ken,

    Another round of thanks for updating your blog post. I read through it and have a much better understanding of using functions in real situations.

    The key point was where you said "I copied the name of the function, then stepped back to the . . ." and provided step-by-step instructions for newbies like me.

    Bravo.

  6. Pingback: Create your own Help for all the Power query functions help in Power BI Desktop | Erik Svensen

  7. Thanks Ken,
    a great post! it there something similar for DAX?
    I am not a fan of the PDF files.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.