With this blog I want to show you how to retrieve data recursively using M, the language behind Power Query.
M is a powerful mashup query language optimized for building queries that mashup data. It is a functional, case sensitive language similar to F#, which can be used with Power BI Desktop, Power Query in Excel.
For this exercise we are going to use data from the Jacksonville SQLServer User Group.
My final report is embedded below and you can interact with it. However, for this exercise we will only focus on creating the steps to get data recursively.
Meetup has an API that provides simple RESTful HTTP and streaming interfaces. You can take advantage of it; However, you will first need to sign up for a free API key. You can get your free API key from here if you want to follow along.
In addition, if you do not have Microsoft Power BI Desktop, you can download it for free from here.
These are the step to get data from Meetup.
Open the Power BI Desktop application.
From either the welcome screen, or the main Home ribbon, click on Get Data.
From the Get Data dialog box, under Other choose Web, as shown below, and click the Connect button.
The From Web dialog box is brought forward.
On the URL input box, enter the following URL, making sure to replace YOUR-KEY with your own API key, and click the OK button.
https://api.meetup.com/members?offset=0&format=json&group_urlname=Jacksonville-SQL-Server-User-Group&page=200&key=YOUR-KEY&order=name
Next few steps will unfold the JSON data into a table.
Clicl List of the results cell.
After that click on the To Table icon from the List Tools ribbon menu and click OK. This action will convert the list into a table.
Next, click on the icon on the right side of column1, as shown below, and click on the OK button. As you can see, for my exercise I am using only few columns.
Create a Function
Now that we have the base query, we will convert it into a function, so that we can call it as many time as we need to.
We need to call the query recursively because for each call we make, we only get 200 records back. And the SQLServer User Group has over 700 members.
This is how we are going to achieve it.
Left Click on Query1 and from the popup menu choose Create Function, as shown below.
Click Create on the subsequent screen. On the Create Function dialog box enter GetMembers in the Function Name input box.
You should now have the following result in front of you. Please note that I've masked out my personal API key.
We need to parameterize the function and make it dynamic.
We will achieve this by modifying the first part of the script as show by the two outline sections on the picture below.
Basically, we are adding the parameter offSet as text between the parentheses, and we are stitching it like this, " & offSet & ", as part of the web call. So to dynamically replace the 0 with the passed in parameter value.
You can now delete Query1 because we no longer need it.
You can test the function if you want by entering an offSet value, for instance 1, and clicking the Invoke button. If you do so, delete the generated query so that it doesn't clutter up your work space.
Create the Main Query
Let us now create the Query that will call the newly created function to retrieve the entire list of Members.
Right mouse click on the Navigator Window and create a new Blank Query as shown below, and rename it Members.
You can easily create a table by entering the following M script on the formula bar.
= Table.TransformColumnTypes(Table.FromRows({{"0"},{"1"},{"2"},{"3"}},{"OffSet"}) ,{{"OffSet", type text}})
This version of the script differs from the one shown in the picture below, because it also transform the column to be of Text type.
Compounding multiple function calls, prevents us from having to change the column type on a subsequent step.
Our function expects a value of type text. However, the original column is of type Any. Long story short, you should end up with something like shown below.
We now need to add a custom column that calls the GetMembers function.
We can achieve this by choosing Add Custom Column from the Add Column menu. And modify the Add Custom Column dialog box by entering the same information as depicted by the below screen shot.
What we did here is basically call the GetMembers function, we created above, for each record in the table, an passing in the Offset value.
Clicking on the Ok button initiates the function call. As you can see for the picture shown below, each record of the newly added column contains a table.
And each table contains 200 records, at least for the first three instances. The last one should be less, because the Jacksonville SQLServer User Group has just over 700 members.
Expand the table by clicking on the icon on the right side of the Members' column, and you will see all the records appear.
You can now remove the Offset column, since it's no longer needed.
My learning exercise went further. In addition to Members I retrieved Events and generated a List of RSVPs to form a sample data mart. The final report is shown below.
I hope that I was able to whet your appetite and left you with the desire to learn more.
Thank you for reading this far and good luck on your learning endeavor.