Forum Discussion

MichaelHutchens's avatar
MichaelHutchens
Helper V
4 years ago
Solved

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 th...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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"