If you’ve been working with Power Query for any amount of time, undoubtedly you have used a web service as a data source (through the Web.Contents call). The next logical progression from that thought is “Now what about applying a web service to each row in a result?”. That’s what this article will cover.
For our sample problem, we are going to create an Excel worksheet where we can maintain a list of SharePoint list names and define a series of steps to call the SharePoint List Web Service (REST) on each list name to pull the item count.
To accomplish this, we need to make use of Power Query Functions. These functions can be called within an “Add Custom Column” step to perform an action against a column of data returning data of a certain type. By making a web service call within this function, we are able to call the web service using data from a column in the worksheet.
For this, we are going to use 3 lists:
- Documents Library – containing 12 items
- CAML Threshold Limit list (used in previous articles) – containing 15,003 items
- Links list – containing 3 items
Web Service Details
To pull the Item Count information, we are going to use the GetByTitle List Web Service with endpoint: <Site URL>/_api/lists/getbytitle(‘<List Name>’).
Before we go into setting up the worksheet and queries, we have to understand the structure of what gets returned when we call the web service.
Calling this web service on the Documents Library yields:
In looking at the structure, we can see that the path to the Item Count is: entry/content/m:properties/d:ItemCount. Also notice the namespaces for the m and d prefixes. They are http://schemas.microsoft.com/ado/2007/08/dataservices and http://schemas.microsoft.com/ado/2007/08/dataservices, respectively. Familiarity with these namespaces will come into play later in the process.
Build the Function
The best way to build a function is to build it as a normal query first and then convert that query to a function. To do this, let’s simply query for the count of all the items in the “Documents” library.
Step 1: Create a new Blank Query
Choose “Blank Query” from New Query > From Other Sources > Blank Query
Step 2: In the Query Editor, call the web service for Documents.
In the Query Editor window, we enter the following to call the Lists Web Service for Documents: = Xml.Tables(Web.Contents(“<Site URL>/_api/lists/getbytitle(‘Documents’)”))
After running this step, you’re Preview Pane will look like this:
You’ll notice the columns category, link, title, updated, author, content, and 2 namespaces are exactly the first level of nodes under the root entry node we saw above. Now the process of expanding columns is synonymous with walking the XML.
As you recall, our path to the Item Count is: entry/content/m:properties/d:ItemCount. So from these columns, the only one we need is content.
Step 3: Keep content column and expand
Select the content column and choose “Remove Other Columns” from the “Remove Columns” action.
After selecting only the content column, expand this column. You’ll notice some column options to choose from:
After the content node, we are wanting to walk down the m:properties node. You’ll recall that the namespace for the m prefix is http://schemas.microsoft.com/ado/2007/08/dataservices. This prefix is what you see as one of the selections. Choose that selection (unchecking the “Use original column name as prefix”) and click “OK”.
Now you will see only the one column that represents the m prefix. Expanding on this column gives you:
Now you can select the column for m:properties. Click OK.
Step 4: Get ItemCount property and finish Function Logic
Expand the properties table to give you:
Once again, this selection represents the namespace for the d prefix. Click OK to choose it.
Expand this table to select the ItemCount property.
Select the ItemCount item, Click OK, and Expand the column.
In this screen, select Element:Text to retrieve the value.
Finally, you now see the ItemCount returned for the Documents library.
Rename the column to complete the function logic.
Step 5: Convert the query to a function and rename the function for specific use
Open up Advanced Editor and observe the M language code.
To convert this to a function, we need to wrap this M language code, as is, with the function definition and make the function take an argument instead of a static list name (we’ve used Documents until now).
Make the changes to the code to reflect this:
As you can see, we wrapped the query code with the function code and changed our web service call to take the parameter listName as its value.
Change the function name to GetListCount and click Close and Load.
Now your function is complete. We can now move on to invoking this function.
Create Worksheet of List Names and Invoke GetListCount Function
Step 1: Create Worksheet of List Names
Quite simply, use one of the worksheets in the Excel document and use a column to list out the SharePoint list names.
Step 2: Create a new Query using the List of SharePoint List Names as the Source
Select “From Table” from the “Data” tab.
In the “Create Table” dialog, select the list of SharePoint links.
In the “Query Editor” window that appears, click “Add Custom Column”
In the “Add Custom Column” dialog, enter “= GetListCount(<Column Name>)” to call the function.
Expand the new Custom Column and select the “Count” column.
The results are show the counts from the Web Service.
Rename the columns, if you wish.
And now, by running the query, you can see the finished result.
This example, in and of itself, is a useful solution for extracting list counts by web service. But the implications of having the ability to apply a web service to each row with open up the world of possibilities to create more rich, more robust, and more dynamic Power Query solutions.