Power Query Functions–Some Scenarios

By Julie Smith

I was happy to co-present a session at this week’s Atlanta BI User Group with Rob Volk (@SQL_R) meeting entitled “Harvesting XML Data from the Web with Power Query and Curl.” The demo gods were not with me on my grand finale demo that night however. I had spent the demo building a Power Query Function and when I tried to invoke it against a list of values, I got a failure which I couldn’t resolve that night. Of course, as soon as I opened the spreadsheet the next day I immediately saw the problem, which I will share here, as I think it is probably going to be something people encounter frequently as they start to work with Power Query.

What the Function Did:

Here’s the setup: www.SQLSaturday.com contains a page for every SQL Saturday, and if it’s available, the schedule for the event. Atlanta’s last event was this month and was SQL Saturday #285-hence, its schedule is located at  http://sqlsaturday.com/285/schedule.aspx. Any other SQL Saturday event number works in the same manner. If I want to use Power Query to view this data as a page, I would enter that url as the source in Power Query:

After choosing Table 1 from the choices on the Navigator pane:

this is how the data appears in the Power Query Query Editor:

and here is a shot of what the M looks like when you go to the Advanced Query Editor (View>Advanced Editor):

Turning the Original Query into a Power Query Function: 

I then copied that M onto my clipboard, and created a new power query (Power Query>From Other Source>Blank Query), pasted that M in the new query and made it into a function called fSQLSatEvent by adding the following code to it:

The blue box is the original M expression we are converting to a function. We add the top line and the bottom line, giving the function the name fSQLSatEvent. EventNum (red box) is the parameter we will pass in for the former literal vaue “285”. What makes Power Query understand that this is a function is the “goes to”  operator, “=>”

I now get this option in the Power Query window, enabling me to Invoke the function on any number:

This gives me the results for Atlanta’s event #111:

HOWEVER, it now makes the Query named fSQLSatEvent no longer a function. It makes it a PQ query which returns the results of that function for the parameter 111. This is where my demo went wrong. I showed how to make this function, invoked it and then left the query in that state.  Then I tried to invoke it in my next demo. The error I got was:

Expression.Error: Cannot convert a value of type Table to type Function. AND I’VE SEEN THAT BEFORE, but for some reason, couldn’t remember it during the presentation–doh!

Pro Tips:

If you have a function in your workbook, and you Invoke it, don’t forget to delete the step where you invoked it if you want to reference the function in another query.

How does one actually invoke a function from another PQ query anyway? Glad you asked. Here is the demo that failed on Monday, without the failing part:

Calling a Power Query Function from Another Query:

After invoking the function, go back and delete the invocation (highlighted in red)

Now go to new query window:

in the expression text box, type

= {1..3}

this will give you a list in Power Query from 1 to 3. (I could enter any two digits between the ..  btw )

Now click on Convert to Table:

The reason that the description on this query reads “Faisal’s demo TechEd” is that in their session from TechEd NA 2014 on Power Query, Miguel Llopis Faisal Mohamood did a similar demo and it is where I learned how to create a list in Power Query. This is not their demo, but I encourage you to watch their session: https://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DBI-B324#fbid=

Now the list becomes a table with one column, named Column1

Right click Column1 and ChangeType from the context menu to Text:

Next click the Insert tab and select Insert Custom Column:

Next the custom column dialog box appears:

Where we type the name of the function, open parenthesis, then click Column1, then click insert to add it to the expression, then close the parenthesis. This invokes the function for each row in the table, passing in the value in Column1 to the EventNum parameter. This gives us a new Custom Column which you can see can be expanded to show the results of each schedule for SQL Saturdays 1, 2, and 3:

Click the symbol beside the Custom column with outfacing arrows and it will expand to see your full results.

and voila! Successfully calling a function against a list of values:

Scraping multiple pages of data from the Internet is one of my favorite capabilities in Power Query–I hope you enjoyed this brief demo of leveraging it.


**Edit 04/29/2016

It seems that now when you create a function in Power Query, the editor prevents you from making the mistake I initially made. Now when I create a function in Power Query, it remains in its own object, regardless of me invoking it. Thanks for that Microsoft.


8 thoughts on “Power Query Functions–Some Scenarios

  1. Great info here! I’m curious – I have a query with information relating to many employees. I created a function so you can type the employees name and the function will return 1 row with all of their pertinent info. Then, I need to reference all of the values in this row in other excel worksheets. Do you know a good approach for this? I’m trying to figure out how to make the function and referencing reusable.

  2. Thanks Datachix, that gave me the missing piece to do something pretty cool. Hope you;ve got over the presentation; shit happens ! 😉

  3. Hi,
    Great article. I have tried to do same at my end. However, I am not getting Table 1 and Table 0. I am only getting Document when I try to ingest data from web page . Are there any other settings ?

Leave a Reply