Data from multiple Sharepoint folder

[avadi]

Hi, 

I have a list in sharepoint like below table. I would like to get data from all the sharepoint folders into one data table in PowerBI.

Is this possible? if yes, can someone help?

Thanks in advance,

avadi

[Martin_D · Accepted solution]

Yes and no. In Power BI Desktop you can automate the loading from a list of SharePoint URLs, but after publishing to Power BI Service, it won't refresh there for security concerns.

If loading the data in Power BI Desktop is sufficient, the solution would be as follows:

1. Use the SharePoint List connector in Power Query and create a query to load one list.
2. Create a text type parameter and enter the URL of this Site.
3. In the query created in step 1. replace the site URL part in the SharePoint connector with the parameter.
4. Click on the query that is now using the parameter and click on create custom function, see https://www.sqlservercentral.com/articles/create-custom-functions-in-power-query 
5. In the query that loads the table with all the site URLs, add a column of type "Invoke custom function", calling the custom function created in step 4. using the column Site_url as parameter value, see https://learn.microsoft.com/en-us/power-query/custom-function#invoke-a-custom-function-as-a-new-colu...
6. Remove the columns Site_name and Site_url as needed.
7. Expand the new column that contains tables. See here just to see what I mean: https://eriksvensen.wordpress.com/2019/10/05/powerquery-control-the-expand-columns-so-it-includes-ne... 

Done.

You might need to confirm security warnings or lower security settings for Power Query in Power BI Desktop to run this query successfully.

If you want to run refresh in Power BI Service, you need to build a query without parameter for each site manually and then combine them using the Append button, see https://learn.microsoft.com/en-us/power-query/append-queries The fastest way to build these queries is to copy and modify the first query you've built.

If you want to keep the solution dynamic and fully automated, you can consider creating a Power Automate flow to read the list of site urls and copy the data from all SharePoint lists into one Data Verse table and load the data verse table into Power BI or you can use Azure Data Fatory and store the combined list into whatever type of table store you prefer and load from there into Power BI.

BR

Martin