Loading multiple SharePoint page libraries into the same query

[MichaelHutchens]

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

I have a single SharePoint site collection with multiple page libraries, one in each site. Each library has identical columns. 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 site names:

How-we-work
Council-news
Our-locations
Wellbeing-and-safety

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

let
  Source = SharePoint.Tables("https://SITE_ADDRESS/council-news/", [Implementation="2.0", ViewMode="All"]),
  #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id = "793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items],
  #"Expanded Author Byline 1" = Table.ExpandTableColumn(#"793529eb-48c3-4fad-a005-7a4aa0a716ad", "Author Byline", {"title"}, {"Author Byline.title"}),
  #"Renamed columns" = Table.RenameColumns(#"Expanded Author Byline 1", {{"Author Byline.title", "Author Byline"}}),
  #"Fix Non List Values" = Table.TransformColumns( #"Renamed columns", {{"Checked Out To", each if Value.Is(_, type list) then _ else null}} ),
  #"Added Custom" = Table.AddColumn(#"Fix Non List Values", "Review Deadline", each ""),
  #"Changed Type" = Table.TransformColumnTypes(#"Added Custom", {{"Review Deadline", type datetime}}),
  #"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {"Title", "Name", "Author Byline", "Modified By", "Modified", "Created By", "Created", "Review Deadline", "Content Type", "Topic", "Audience", "Expiration date", "Authoring Canvas Content", "Banner Image URL", "Description", "First Published Date", "Page Layout Content", "Checked Out To", "File Size", "Comment count", "Like count", "Version"}),
  #"Expanded Checked Out To" = Table.ExpandListColumn(#"Removed Other Columns", "Checked Out To")
in
    #"Expanded Checked Out To"

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

Thanks in advance for your time 🙂

[DataInsights · Accepted solution]

@MichaelHutchens,

Try this solution. The concept is to create a URL table with a row for each library, and create a custom function that will loop through each library.

https://www.howtoexcel.org/how-to-extract-data-from-multiple-webpages/ 

[v-luwang-msft]

Hi @MichaelHutchens ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[DataInsights · Accepted solution]

@MichaelHutchens,

Try this solution. The concept is to create a URL table with a row for each library, and create a custom function that will loop through each library.

https://www.howtoexcel.org/how-to-extract-data-from-multiple-webpages/ 

[MichaelHutchens]

Thanks @DataInsights , that looks promising. I've tried the above but I'm stuck on an error message:

Here's the code from the fGetWikiResults table:

let GetResults=(URL) =>

let
  Source = SharePoint.Tables("URL", [Implementation="2.0", ViewMode="All"]),
  #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id = "793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items]
in
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad"

in GetResults

And here's the code from the URL_List table:

let
    Source = Excel.Workbook(File.Contents("C:\LOCATION\Kotahi Mahi - General\Reporting\PowerBI\URL_Table_Kotahi_Pages.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"URL", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Results Data", each fGetWikiResults([URL]))
in
    #"Added Custom"

Here's the data from the spread sheet:

Any idea on what I might be doing wrong?

[KNP]

Hi @MichaelHutchens,

I think you're nearly there. Just need to tweak your function slightly...

Remove the quotes from URL, e.g. SharePoint.Tables(URL... not SharePoint.Tables("URL"...

It is referencing the URL passed into the function so the quotes are not required.

Also, in the image above, in the blue box, I might be missing something but I don't think that is going to give you the dynamic result you are looking for, so if you are having issues, maybe try removing that from the function also.

[MichaelHutchens]

Thanks @KNP & @DataInsights, removing the ""s worked for me 🙂

@KNP : I did have an issue when I went to set a scheduled refresh on this report after I published it:

So I'm going back to remove the reference in the blue box as you suggested:

let GetResults=(URL) =>

let
  Source = SharePoint.Tables(URL, [Implementation="2.0", ViewMode="All"])
in
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad"

in GetResults

But its now giving me this error:

So I tried to go back and modify the code again, and I'm getting another error. Would you have any idea how I might fix this? I'm starting to dig myself a hole 😄

[KNP]

Try 

let GetResults=(URL) =>

let
  Source = SharePoint.Tables(URL, [Implementation="2.0", ViewMode="All"])
in
    Source

in GetResults

But I don't think that is your issue now.

The 'Dynamic data source' error is one I'm not sure you'll be able to solve with the SharePoint.Tables connector. I've not needed to solve this problem myself so far so I haven't spent enough time on it to know if there are any workarounds. The workaround for other connectors exist i.e. Web.Contents using [RelativePath] but I'm not sure how easy this would be to incorporate into this example.

Maybe @DataInsights may have some more ideas in this space. 

[DataInsights · Accepted solution]

@MichaelHutchens,

Try using Web.Contents as suggested by @KNP:

let GetResults=(URL) =>

let
    Source = Web.Page(Web.Contents(URL)),
    Data1 = Source{1}[Data]
in
    Data1

in GetResults

If this doesn't work, try Source{0} instead of Source{1}.

[MichaelHutchens]

Thanks so much @DataInsights & @KNP for your time with this, I really appreciate it 🙂

[DataInsights]

@MichaelHutchens,

Try removing the double quotes from "URL" in the Source step:

let GetResults=(URL) =>

let
  Source = SharePoint.Tables(URL, [Implementation="2.0", ViewMode="All"]),
  #"793529eb-48c3-4fad-a005-7a4aa0a716ad" = Source{[Id = "793529eb-48c3-4fad-a005-7a4aa0a716ad"]}[Items]
in
    #"793529eb-48c3-4fad-a005-7a4aa0a716ad"

in GetResults