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.
Can you share the m code?
rubayatyasmin sure see below.
let
// Get the list of SharePoint sites and corresponding list names
Source = SharePoint.Tables("https://ourdomain.sharepoint.com", [ApiVersion = 15]),
SitesList = Source{[Name="Site Sign In"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(SitesList,{{"Site URL", type text}, {"List Name", type text}, {"Tenant Name", type text}, {"Site Code", type text}}),
// Add a custom function to fetch data from the corresponding SharePoint list for each SharePoint site
FetchList = (site as text, list as text) =>
let
Source = SharePoint.Tables(site, [ApiVersion = 15]),
List = Source{[Name=list]}[Content]
in
List,
// Add a new column "Custom" to call the custom function for each row
AppendLists = Table.AddColumn(#"Changed Type", "Custom", each FetchList([Site URL], [List Name])),
// Expand the tables from the "Custom" column into rows
#"Expanded Custom" = Table.ExpandTableColumn(AppendLists, "Custom", {"Title", "Profession"}, {"Title", "Profession"}),
// Select only the necessary columns
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Tenant Name", "Site Code", "Title", "Profession"})
in
#"Removed Other Columns"Hi rubayatyasmin any ideas on why it isn't working?
The second line after source { "site sign ..... } You should be able to see the list of contents of your SP site. In your first step can you see the initial view at least? Also, make sure you are using the correct list name.
Hi rubayatyasmin it appears to be failing on the last line of the code
In #"Removed Other Columns"It doesn't give an error when I use
in SourceHi rubayatyasmin ,
I think the issue is that I am not understanding and I am new to Power BI
With just having the first two lines this is what I am seeing, I have tried several names for the SitesLists but no luck.
Hey, you want to read some specific files right? that are in a folder inside SharePoint? Use SharePoint folder connector.
Power Query SharePoint folder connector - Power Query | Microsoft Learnalso in
in Source should be SitesList because that is the last step.Hi rubayatyasmin ,
No I am looking to read a specific list on each Sharepoint site.
We have a "Site Sign In" list on each site that has the same colmuns and I want to be able to read specific colums or all columns so I then can generate a table to show them from all site together.
John
Hi jbrines
Then use SharePoint list connector. Here is the document that can help you.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list