Forum Discussion

MichaelHutchens's avatar
MichaelHutchens
Helper V
4 years ago
Solved

Loading multiple SharePoint document tables into the same query

Hi folks, I'm hoping someone might be able to assist.

I have a single SharePoint site with multiple document libraries. Each library has identical columns, but is used for different document types. I'm trying to find an elegant way to bring every library into the same PowerBI query for modelling, rather than loading each library separately. Here are my libraries:

 

And here is my current code after I've loaded the first library ("Communications"):

 

 

let
    Source = Table.Combine(
        {
        SharePoint.Tables("https://SITE_URL_GOES_HERE/", [ApiVersion = 15])
        }
    ),
    #"cbbe7c42-9d75-4d3e-9bd9-4260ef76c25f" = Source{[Id="cbbe7c42-9d75-4d3e-9bd9-4260ef76c25f"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"cbbe7c42-9d75-4d3e-9bd9-4260ef76c25f",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

 

 


Is there a way to modify this code to load the 4 libraries above?

Thanks in advance for your time 🙂

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can filter based on [Title] and then expand on [Items] like this:

    let
    Source = Table.Combine(
        {
        SharePoint.Tables("https://SITE_URL_GOES_HERE/", [ApiVersion = 15])
        }
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each
        ([Title] = "Communications" or
         [Title] = "Forms" or
         [Title] = "Guides" or
         [Title] = "Images"
        )
    ),
    #"Expanded Items" = Table.ExpandTableColumn(
        #"Filtered Rows", "Items",
        {"Title", "Created"},
        {"Title", "Created"}
    )
in
    #"Expanded Items"

     Replace "Title" and "Modified" with whatever columns you actually want.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You can filter based on [Title] and then expand on [Items] like this:

    let
    Source = Table.Combine(
        {
        SharePoint.Tables("https://SITE_URL_GOES_HERE/", [ApiVersion = 15])
        }
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each
        ([Title] = "Communications" or
         [Title] = "Forms" or
         [Title] = "Guides" or
         [Title] = "Images"
        )
    ),
    #"Expanded Items" = Table.ExpandTableColumn(
        #"Filtered Rows", "Items",
        {"Title", "Created"},
        {"Title", "Created"}
    )
in
    #"Expanded Items"

     Replace "Title" and "Modified" with whatever columns you actually want.