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:
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!
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
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:
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:
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.
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.