Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
avadi
New Member

Data from multiple Sharepoint folder

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

 

 

avadi_0-1702538855773.png

 

1 ACCEPTED SOLUTION
Martin_D
Super User
Super User

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

View solution in original post

1 REPLY 1
Martin_D
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors