Forum Discussion
How to combine SharePoint List from multiple SharePoint sites
I cannot share the pbix sample but I can provide the explanation of the code:
The code is designed to fetch data from multiple SharePoint lists based on the URLs and list names provided in an Excel file.
The first part of the code reads an Excel file. This Excel file contains a list of SharePoint site URLs and corresponding list names. The Excel.Workbook function is used to load the Excel file, and the Table.RenameColumns function is used to rename the columns to "Site URL" and "List Name".
Next, a custom function named FetchList is defined. This function takes a SharePoint site URL as an argument and returns a table of all lists in the SharePoint site. The SharePoint.Tables function is used to fetch the lists from the SharePoint site.
Finally, a new column named "Custom" is added to the table. For each row in the table, the FetchList function is called with the site URL from that row. The result is a table with an additional column that contains the SharePoint lists for each site URL.
This code is a common pattern in Power Query M language for fetching and combining data from multiple sources based on a list of parameters.
After editing your code with my own data, I was able to import multiple lists into Power BI. Howerver, I am stuck with refreshing the Dataset in Power BI Service because it does not support dynamic data source refreshing. I am aware of the solution for the Web.Contents function using RelativePath and Query headers but I am getting the authorization error when trying to use Web.Contents to fetch SharePoint lists even though I am admin of the SharePoint. Any suggestions on how to refresh dynamic data sources in Power BI Service when using SharePoint related function in Power Query?
Hello Samper,
Can you please share your code, I am not able to fetch the lists.
Avian
Sure here is my code:
let // Get the list of SharePoint sites and corresponding list names ParameterTable = Excel.Workbook(File.Contents("C:\myparameters.xlsx"), null, true), Sheet1_Sheet = ParameterTable{[Item="Sheet1",Kind="Sheet"]}[Data], #"Renamed Columns" = Table.RenameColumns(Sheet1_Sheet,{{"Column1", "Site URL"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Site URL", type text}, {"Column2", type text}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Column2", "List Name"}}), // Add a custom function to fetch data from the corresponding SharePoint list for each SharePoint site FetchList = (site as text) => let Source = SharePoint.Tables(site, [ApiVersion = 15]) in Source, // Add a new column "Custom" to call the custom function for each row AppendLists = Table.AddColumn(#"Renamed Columns1", "Custom", each FetchList([Site URL])) in AppendListsand in the first comumn named "Site URL" of my parameter table, I have urls that look like this:
https://my.sharepoint.com/sites/AO1_FND
https://my.sharepoint.com/sites/AO1_FFF
and so on.Unfortunately, refreshing dynamic data sources is not possible in Power BI Service and if I use the function Web.Contents I face the authentication error even though I am the admin of the SharePoint and I can import the data using the code above. I am so lost and so puzzled that such an issue does not have a straighforward solution. Maybe uploading the data to Dataverse could solve this issue?
Hello Samper,
Following function is not clear to me, you have column name "site url" which is fetching from excel. How this site url is relating with folllowing function, can you please explain anf if posible please share the sample power BI for more clarity.
// Add a custom function to fetch data from the corresponding SharePoint list for each SharePoint site FetchList = (site as text) =>
let Source = SharePoint.Tables(site, [ApiVersion = 15])
in Source
Regards
Avian
I cannot share the pbix sample but I can provide the explanation of the code:
The code is designed to fetch data from multiple SharePoint lists based on the URLs and list names provided in an Excel file.
The first part of the code reads an Excel file. This Excel file contains a list of SharePoint site URLs and corresponding list names. The Excel.Workbook function is used to load the Excel file, and the Table.RenameColumns function is used to rename the columns to "Site URL" and "List Name".
Next, a custom function named FetchList is defined. This function takes a SharePoint site URL as an argument and returns a table of all lists in the SharePoint site. The SharePoint.Tables function is used to fetch the lists from the SharePoint site.
Finally, a new column named "Custom" is added to the table. For each row in the table, the FetchList function is called with the site URL from that row. The result is a table with an additional column that contains the SharePoint lists for each site URL.
This code is a common pattern in Power Query M language for fetching and combining data from multiple sources based on a list of parameters.