Forum Discussion
Loading multiple SharePoint page libraries into the same query
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 🙂
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/
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 GetResultsIf this doesn't work, try Source{0} instead of Source{1}.
9 Replies
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/
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 GetResultsAnd 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?
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
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
