Forum Discussion
Combining multiple SharePoint SitePage libraries into a single query
Hi everyone, I manage an intranet with 10 subsites. I'm looking to create a single PowerBI table that combines basic information from every library, without having to load every single library and then append them into one query.
Here's my code for the first library:
let
Source = SharePoint.Tables("https://URL/", [Implementation=null, ApiVersion=15]),
#"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id="793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"793529eb-48c3-4fad-a005-7a4aa0a716ad",{{"ID", "ID.1"}}),
#"Expanded File" = Table.ExpandRecordColumn(#"Renamed Columns", "File", {"LinkingUri", "LinkingUrl", "Name", "ServerRelativeUrl", "Title"}, {"File.LinkingUri", "File.LinkingUrl", "File.Name", "File.ServerRelativeUrl", "File.Title"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded File",{"File.ServerRelativeUrl"})
in
#"Removed Other Columns"
My question is, is it possible to modify the code above to include these additional SitePage libraries (as an example):
Any alternate suggestions about how to load multiple similar tables from different SharePoint sites into one PowerBI reporting table would be much appreciated 🙂 Thanks so much 🙂
Then you should be able to write a query along these lines:
let Source = Table.Combine( { SharePoint.Tables("https://URL/sites/sitename1/", [ApiVersion = 15]), SharePoint.Tables("https://URL/sites/sitename2/", [ApiVersion = 15]), SharePoint.Tables("https://URL/sites/sitename3/", [ApiVersion = 15]) } ), #"Filtered Rows" = Table.SelectRows(Source, each ([Title] = "Site Pages")), #"Expanded Items" = Table.ExpandTableColumn(#"Filtered Rows", "Items", {"File"}, {"File"}), #"Expanded File" = Table.ExpandRecordColumn(#"Expanded Items", "File", {"ServerRelativeUrl"}, {"File.ServerRelativeUrl"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded File",{"File.ServerRelativeUrl"}) in #"Removed Other Columns"
4 Replies
I'm pretty sure you'll have to call SharePoint.Tables("URL",...) for each separate site but you can still do that in a single query if you want to.
It looks like you're loading a specific list. Is there an analogous one in each site with the same/similar Title?
Hi AlexisOlson , correct the above is a specific list. Here's a sample of three more analogous ones from each site:
https://URL/sites/sitename1/SitePages/
https://URL/sites/sitename2/SitePages/
https://URL/sites/sitename3/SitePages/Then you should be able to write a query along these lines:
let Source = Table.Combine( { SharePoint.Tables("https://URL/sites/sitename1/", [ApiVersion = 15]), SharePoint.Tables("https://URL/sites/sitename2/", [ApiVersion = 15]), SharePoint.Tables("https://URL/sites/sitename3/", [ApiVersion = 15]) } ), #"Filtered Rows" = Table.SelectRows(Source, each ([Title] = "Site Pages")), #"Expanded Items" = Table.ExpandTableColumn(#"Filtered Rows", "Items", {"File"}, {"File"}), #"Expanded File" = Table.ExpandRecordColumn(#"Expanded Items", "File", {"ServerRelativeUrl"}, {"File.ServerRelativeUrl"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded File",{"File.ServerRelativeUrl"}) in #"Removed Other Columns"
Perfect, thank you so much AlexisOlson! 🙂
