Power BI has built-in connectors to pull SharePoint data from specific SharePoint lists and sites; however, sometimes the data required lives across a dynamic number of sites. Although Power BI queries can be modified to query multiple sites dynamically, when publishing this report to the Power BI Service, you will find it is not supported. Generally, we build Power BI reports with the intent to deploy and share them online, so this becomes a show stopper.
As PPM consultants, the most common place we encounter this scenario is with customizations added to the Risks or Issues lists in Project Online. Columns added to these SharePoint lists do not feed to the Project Online ODATA feed, and as each Project has its own site, querying the custom data into Power BI is very difficult. That is, it was until you found this article!
The key to getting this data is to utilize the SharePoint Search API as your data source. Instead of dynamically querying all the sites, we can simply use SharePoint search to search for all the items we need. This method will work for Customized PWA Risks, Issues, Deliverables, and any Custom lists you have added to your Project Site Templates. Or, any similar scenario where you need to query across multiple sites for certain data.
Setup – Managed Properties
The first thing you will need to set up, any custom columns you need returned in the query will have to be “managed properties” configured in the SharePoint Search settings. This will require SharePoint Administrator access; this is the only drawback to this method, you will need the ability to add these managed properties, it also may take a few days for the first full crawl to populate these fields. As many articles are covering this topic, we will not cover it here, but reach out if you need help!
The Query
Once you have all your managed properties configured, you need to build your search query. You can read about the SharePoint query language here, use one of the several tools available, or you can just steal and modify my query below to your liking.
ParentLink:Risks AND contentclass:STS_ListItem
The easiest way to test your query, in my opinion, is to use the built-in SharePoint Query Builder. You can open this by navigating to Site Settings > Manage Result Sources > New Result Source > Launch Query Builder.
Bringing into Power BI
Now we are ready to pull this data into Power BI; I will be showing you how to do this starting with a blank query and using the advanced editor. We will use the “Web.Contents” method to call the Sharepoint Search API and then the “Json.Document” method to parse the result. I prefer to do this all in a single line:
image=”10159″ ]The URL you will need to call is:”<site>/_api/search/query?” with the following parameters:
- “querytext”
- The search query we just built in the last step
- “selectproperties”
- A comma separated list of the properties you wish to return (usually the column names)
- “rowlimit” & “rowsperpage”
- I prefer to set both of these to the max of 500
- “TrimDuplicates”
- I set this to false for my use case as there could be the same risks across multiple projects or other scenarios where I want the duplicates.
- Headers
- You will also need to pass the accept header “application/json”
Here is the full line for easy copy/paste:
Source = Json.Document(
Web.Contents(
"https://advisiconinc.sharepoint.com/sites/pwa-demo1/_api/search/query?
querytext='ParentLink:Risks AND contentclass:STS_ListItem'&
rowlimit=500&
rowsperpage=500&
selectproperties=
'Title,
Author,
Path,
Description,
Write,
LastModifiedTime,
ParentLink,
SPWebUrl,
UniqueId'&
TrimDuplicates=false",
[Headers=[Accept="application/json"]]
)
)
Use your organizational account as the authentication method; if all has worked correctly, you should get an initial result that looks like this:
At this point, all that is left to do is query manipulation to make your results usable. For those of you that don’t like M query, you can do this all in the Power BI interface at this point, as it is quite a few steps, I will post the result in its entirety here. You could certainly make this cleaner, and you may not need some of the steps at the end depending on your column selection:
/*
Created by Kevin Brouder
Advisicon, Inc. - Helping You Build a Project Management Culture
*/
let
Source = Json.Document(
Web.Contents(
"https://advisiconinc.sharepoint.com/sites/pwa-demo1/_api/search/query?
querytext='ParentLink:Risks AND contentclass:STS_ListItem'&
rowlimit=500&
rowsperpage=500&
selectproperties=
'Title,
Author,
Path,
Description,
Write,
LastModifiedTime,
ParentLink,
SPWebUrl,
UniqueId'&
TrimDuplicates=false",
[Headers=[Accept="application/json"]]
)
),
PrimaryQueryResult1 = Source[PrimaryQueryResult],
RelevantResults = PrimaryQueryResult1[RelevantResults],
Table = RelevantResults[Table],
#"Converted to Table" = Record.ToTable(Table),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Cells"}, {"Value.Cells"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Value1",{"Value.Cells"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
#"Expanded Value.Cells" = Table.ExpandListColumn(#"Added Index", "Value.Cells"),
#"Expanded Value.Cells1" = Table.ExpandRecordColumn(#"Expanded Value.Cells", "Value.Cells", {"Key", "Value", "ValueType"}, {"Value.Cells.Key", "Value.Cells.Value", "Value.Cells.ValueType"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value.Cells1",{"Value.Cells.ValueType"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value.Cells.Key]), "Value.Cells.Key", "Value.Cells.Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","{","",Replacer.ReplaceText,{"UniqueId"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"UniqueId"}),
#"Lowercased Text" = Table.TransformColumns(#"Replaced Value1",{{"UniqueId", Text.Lower, type text}})
in
#"Lowercased Text"
Relationship to Project Online
If you were following along to pull Risk, Issue, or Deliverable data from your Project Online, good news, joining this data to your other Project Online ODATA queries is very easy! For example, the “UniqueId” column will join to the “RiskId” column in the Project Online ODATA Risks table.
If you are using a custom list, but still with Project Online, you can still join this data as well. You will want to utilize the site URL as your key to joining the Project.
Other Considerations – Paging
The major consideration not covered by this article is paging. If you will recall our query is limited to the first 500 results. If you anticipate your environment will grow over this number, or just want to be sure you have a robust report, you will want to implement a paging mechanism. Many articles are covering the topic of paging in Power BI Queries, and as I suspect Power BI updates may change the recommended approaches in the future, we will not cover that here.
