Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 🙂
Solved! Go to Solution.
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"
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"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |