Forum Discussion
Loading multiple SharePoint page libraries into the same query
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}.
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 GetResultsHi 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.
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 😄
