One of the challenges we occasionally run into is how to report on data that is stored in multiple SharePoint sites. Commonly you will see this in structures where there may be multiple sites created for each project, team, process, etc. For example, Project Server and Project Online can create dedicated SharePoint sites for each project. Similarly, Microsoft Teams will create a dedicated SharePoint site for each Team. On each of these sites, you may have one of more lists that track things like Risks, Issues, Invoices, Meeting Minutes, Action Items, Budgets, etc. The challenge comes in with Power BI or more specifically Power Query. When you make a SharePoint list data connection – it’s specific to a particular site and list. In order to report on multiple lists, you could add a connection to each manually but that really isn’t a scalable solution. I personally wouldn’t want to maintain something like that – so instead we are going to create a dynamic solution using the Power Query language, M.
What is the M language?
The M language is the query language used to write queries in Power BI and Power Query. It’s also known as Power Query Formula Language. An M query follows this structure:
Let
Variablename = expression,
#”Variable name” = expression2
In
Variablename
The “Let” statement contains the query formula steps you will use to get your data and transform it as needed. The output is defined in the “In” statement.
For example, when you create a SharePoint list query using the Power BI Get Data wizard, it will look something like this:
let
Source = SharePoint.Tables(“https://organization.sharepoint.com/sites/demo1”, [ApiVersion = 15]),
#”6eb73c72-2c68-48d2-b00b-b991f046a3b4″ = Source{[Id=”6eb73c72-2c68-48d2-b00b-b991f046a3b4″]}[Items],
#”Renamed Columns” = Table.RenameColumns(#”6eb73c72-2c68-48d2-b00b-b991f046a3b4″,{{“ID”, “ID.1”}})
in
#”Renamed Columns”
Let’s break this down:
- The first step in the Let statement is specifying the URL of the SharePoint site.
- The second step in specifying the unique ID of the list (not very user friendly – we’ll address this later).
- The third and last step is renaming the ID column in the list. This is by design – SharePoint returns an “ID” field and an “Id” field – since the data model in Power BI is case insensitive, it renames the ID field to ID.1.
That is a very basic and quick look at the structure of an M query. Now let’s get back to the task at hand – how can we make this dynamic so we can query more than one site at a time?
Let’s start with a blank query in Power BI:
Open Power BI Desktop
Click on Get Data > Blank Query
On the Ribbon, click on Advanced Editor
For the first step in our query, we want to get a list of SharePoint site URLs. In this example, I’m going to use Project Online’s Project Odata feed. This will get us a list of projects and their project site URLs. Enter the following for the Source step replacing SharePointSite with your PWA site name:
Source = OData.Feed(“https://SharePointSite/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl”)
Click on Done
If prompted, click on Edit Credentials and enter your credentials for PWA (or whichever data source you are using).
When the query executes, you should see results similar to below:
In the Properties panel, change the Name of your Query to GetAllInvoices
Click on File > Apply
Great! We have a list of projects and their SharePoint site URLs. Now how do we get Power BI to query a list on each of those sites? Let me give you a hint – the word EACH in our previous sentence is key. That is because there is a command in the M Language named EACH which allows us to call a function for each row that was returned in our first step.
Let’s build our function first and then we’ll go back to using the EACH command in our GetAllInvoices query.
In the Power Query Editor, click on New Source > Blank Query
On the Ribbon, click on Advanced Editor
Enter the following:
(siteURL,listname) =>
let
Source = SharePoint.Tables(siteURL),
#”MyListData” = Source{[Name=listname]}[Content]
in
#”MyListData”
Let’s walk through our function and explain what each step is doing:
- The first line is creating two parameters called siteURL and listname. We’ll use these to pass values from our master project list into our function.
- Lines 2 through 4 define our Let structure. Source is calling the SharePoint list connector and telling it to connect to the site specified in siteURL. #”MyListData” is retrieving the content/list items from the list specified in listname.
- Lines 5 and 6 define what is the output – in this case all the list items from listname in the site SiteURL.
In the Properties panel, change the Name of your Query to GetList
Click on File > Apply
Now we’ll go back to our main query, GetAllInvoices and use EACH to call our GetList function for each SharePoint site.
In the Queries pane, click on GetAllInvoices
On the Ribbon, click on Advanced Editor
At the end of our Source line, add a comma and hit enter
Hit TAB once and enter the following:
#”AllListData” = Table.AddColumn(Source, “Invoices”, each GetList([ProjectWorkspaceInternalUrl], “Invoices”))
In the In statement, change Source to #”AllListData”
Your query should now look like this:
let
Source = OData.Feed(“https://SharePointSite.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl”),
#”AllListData” = Table.AddColumn(Source, “Invoices”, each GetList([ProjectWorkspaceInternalUrl], “Invoices”))
in
#”AllListData”
Click on Done.
The query will run and when complete you should see a new column called Invoices with a value of Table for each row, similar to below:
In your new column, you may see Error. Usually that means that it could not find the list on that particular site. Let’s add another step to remove those sites.
At the end of our #”AllListData” line, add a comma and hit enter
Add the following:
#”Removed Errors” = Table.RemoveRowsWithErrors(AllListData)
In the In statement, change #”AllListData” to #”Removed Errors”
Your query should now look like this:
let
Source = OData.Feed(“https://m365x642157.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl”),
#”AllListData” = Table.AddColumn(Source, “Invoices”, each GetList([ProjectWorkspaceInternalUrl], “Invoices”)),
#”Removed Errors” = Table.RemoveRowsWithErrors(AllListData)
in
#”Removed Errors”
You should not see any Errors in your Invoices column.
Now, let’s expand our Invoices Table to get the individual items from each site. To do this, click on the Expand button 
and uncheck (Select All Columns) and select just the columns you need. In my case, I want Invoice and Amount (see below)
Click on OK
When we use the Expand Table Column command, it creates a new row for each item in the list. As a result, you should see a row per project (in this case) for each invoice like this:
Your query should now look like this:
let
Source = OData.Feed(“https://m365x642157.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl”),
#”AllListData” = Table.AddColumn(Source, “Invoices”, each GetList([ProjectWorkspaceInternalUrl], “Invoices”)),
#”Removed Errors” = Table.RemoveRowsWithErrors(AllListData),
#”Expanded Invoices” = Table.ExpandTableColumn(#”Removed Errors”, “Invoices”, {“Invoice”, “Amount”}, {“Invoices.Invoice”, “Invoices.Amount”})
in
#”Expanded Invoices”
We now have our query built, so click on Close and Apply and let’s go back to the Power BI canvas.
Now let’s add some visuals and test out our data!
In the Visualizations pane, click on Clustered Column Chart
In the Fields pane, drag Invoices.Amount to Value and drag ProjectName to Axis. It should look like this:
Note that it is doing a Count of Invoices.Amount and not a Sum. If I want to know the total amount invoiced on each project, I need it to be a Sum. Under Value, click the drop down arrow next to Count of Invoices.Amount. Note that you do not see a Sum option. When you see behavior like this, check your data type for that field. In this case, Invoices.Amount is set as Text when it should be Currency. Let’s fix that…
In the Fields pane, click on Invoices.Amount and on the Ribbon, change Data type to Decimal Number. Click Yes if prompted.
Next, change the Format to Currency and whichever currency symbol you need.
Now go back to Invoices.Amount under Value and change it to Sum.
Your chart should know show a Sum similar to below:
Next let’s add a Table below our chart and check ProjectName, Invoice.Invoice and Invoice.Amount
Your report should look like this:
Finally, let’s add a Slicer for ProjectName so we can filter for each Project:
Some final thoughts:
Depending on the number of sites and the amount of data returned, you will want to consider:
- Use Scheduled Refresh
- You may have to rewrite your query to increase timeout settings
Happy aggregating!
