Aggregating SharePoint List Data in Power BI

Aggregating SharePoint List Data in Power BI

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:

  1. The first step in the Let statement is specifying the URL of the SharePoint site.
  2. The second step in specifying the unique ID of the list (not very user friendly – we’ll address this later).
  3. 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:

  1. 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.
  2. 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.
  3. 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:

  1. Use Scheduled Refresh
  2. You may have to rewrite your query to increase timeout settings

Happy aggregating!

Neall Alcott
1 Comment
  • Ganga Gurung
    Reply
    Posted at 5:35 pm, August 13, 2020

    Hi Marque,

    I am using your method to get all sharepoint list from multiple sites and it works fine. But I cannot setup schedule refresh for this report. It is saying function doesnt support schedule refresh.

    Can you please guide ?

    Thank you
    Ganga

Post a Comment

Comment
Name
Email
Website