How to combine SharePoint List from multiple SharePoint sites

[avi081265]

Hello 

I want to combine SharePoint List from different SharePoint sites. All the list having same kind of structure. My Idea that I will create one SharePoint LIst which we will have four columns i.e. Tenant Name,SIte Name,List Name and Site code. Then I want to read the data from this list and append or merge all the list which are exist in above list.

Please let me know how can I achieve this. If any article or Video please let me know.

Regards

Avian

[avi081265]

I also tried following, but it is showing Token Eof

Not sure what is the issue?

However original question is still open

[rubayatyasmin]

@avi081265 

Where is the in? did you use in? 

like

in

#"Filtered Rows"

also, modify the code this way,

let Combined = Table.Combine({SharePoint.Tables("https://main.sharepoint.com/site1", [ApiVersion = 15]){[Name="ProjectList"]}[Content], SharePoint.Tables("https://main.sharepoint.com/site2", [ApiVersion = 15]){[Name="ProjectList"]}[Content]}) in Combined

[rubayatyasmin]

Hi, @avi081265 

To combine SharePoint lists from different SharePoint sites, you can use Power Query in Power BI. Power Query allows you to connect to various data sources, including SharePoint lists, and perform data transformations.

Here is a step-by-step guide on how to achieve this:

1. Open Power BI Desktop and click on "Get Data" from the Home tab.

2. In the "Get Data" window, search for and select "SharePoint Online List" as the data source.

3. Enter the URL of one of the SharePoint sites containing the lists you want to combine. Click OK.

4. In the Navigator window, select the SharePoint list you want to import and click Edit. This will open the Power Query Editor.

5. In the Power Query Editor, you can perform any necessary data transformations, such as removing unnecessary columns or filtering rows. Make sure the column names and data types match across all the lists you want to combine.

6. Once you've finished transforming the data, click Close & Apply to load the data into Power BI.

7. Repeat steps 3 to 6 for each SharePoint site and list you want to include in the combined list.

8. After importing data from all the SharePoint lists, you will have multiple queries in the Power Query Editor. To merge or append the data, you can use the "Append Queries" or "Merge Queries" options from the Home tab.

   • Append Queries: Use this option to stack the rows of multiple tables vertically. The column names and data types must match for successful appending.

   • Merge Queries: Use this option when you have a common key column in all the queries and want to combine the columns horizontally based on that key.

9. Once you have appended or merged the queries, you can further refine the combined list if needed.

10. Finally, click Close & Apply to load the combined data into Power BI. You can now use this data to create visualizations and reports.

[avi081265]

Hello Rubayart,

Thanks for quick response. I already followed these steps. As i mentioned earlier that I am maintain a list with site url and list name. I want that in what ever siteurl and list available in this list, should populate a common dataset instead of adding of each site and append.

Logic behind that, today I have 4 sites, in fuure I have more sites, so I have to add/update the url in particular list.  so I need to update report every time.

Can you please guid here that how can I read the site url from particular site url list and combine or append all the site url in one dataset.

Thanks again

Avian 

[rubayatyasmin]

try this one. 

let
    // Get the list of SharePoint sites and corresponding list names
    Source = SharePoint.Tables("https://main.sharepoint.com", [ApiVersion = 15]),
    SitesList = Source{[Name="SitesList"]}[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", {"Employee ID", "Name", "Position"}, {"Employee ID", "Name", "Position"}),

    // Select only the necessary columns
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Tenant Name", "Site Code", "Employee ID", "Name", "Position"})
in
    #"Removed Other Columns"

This script will:

1. Fetch the list of SharePoint sites and their corresponding list names from your main SharePoint site.
2. For each SharePoint site, it fetches the corresponding SharePoint list.
3. It then expands each fetched SharePoint list into rows and appends these rows into the main table.
4. Finally, it selects only the necessary columns.

Please replace "Employee ID", "Name", "Position" with your actual column names in the SharePoint Lists. If your lists have different structures, you'll need to modify the script accordingly to accommodate those differences.

Also, please replace "https://main.sharepoint.com" with your main SharePoint site URL and "SitesList" with your actual list name that contains the SharePoint site URLs and corresponding list names.

this is an experimental solution. you need to adjust it. 

[jbrines]

@rubayatyasmin Is it possible to just have it show all columns of the sharepoint list, if so what would the code be.

I tried your code but I was just getting an error

Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Name=Site Sign In
Table=[Table]

J

[rubayatyasmin]

Hi @jbrines thanks for reaching out. Can you tell me in which step you are getting the error? And yes it's possible to read the columns from SP list. Also, make sure you have replaced the necessary changes. 

[jbrines]

Hi @rubayatyasmin see screenshot below.

If I can get this working then I won't need the code for all columns

J

[rubayatyasmin]

Can you share the m code?

[jbrines]

@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"

[jbrines]

Hi @rubayatyasmin any ideas on why it isn't working?

[rubayatyasmin]

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. 

[jbrines]

Hi @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.

[rubayatyasmin]

also in 
in Source should be SitesList because that is the last step. 

[rubayatyasmin]

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 Learn

[jbrines]

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

[rubayatyasmin]

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

[jbrines]

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
    Source

[Anonymous]

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?

[avi081265]

Hello Samper,

Can you please share your code, I am not able to fetch the lists.

Avian