cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Reoccuring export of SharePoint List (Monthly snapshot)

Hi, I am trying to create a monthly snapshot of a SharePoint list for reporting purposes. Ultimately, I want to import this into Power BI. 

 

So far, I have found a way to extract the information into an excel table using MS Flow every month with a timestamp for each row (See attached picture). However, even though the excel sheet is only 338 rows, it uses a very long time in doing so. Does anyone know how I can make this go faster (or why it is so slow)? Or alternatively, another way of getting the same result?

 

PS. it is the 'Apply to each' section that is taking a long time.

 

Kind regards, 

Jon

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Reoccuring export of SharePoint List (Monthly snapshot)

My solution was to use the sharepoint to CSV template in MS Flow and then connect to the sharepoint folder via Power BI. 

View solution in original post

5 REPLIES 5
Highlighted
Power Participant
Power Participant

Re: Reoccuring export of SharePoint List (Monthly snapshot)

Hi @JJJ94,

 

There is way to directly connect SharePoint list to Microsoft PowerBI as datsource. So there is no need to utilize Flow to export data to excel and then import them back to PBI.

Here is article about how to do this as there is no way to do it from web PBI, you will need desktop app for this - https://community.powerbi.com/t5/Power-BI-Language-Translation/Power-BI-Get-Data-From-SharePoint-Lis...

 

And of course you can build your report upon this. Btw there are some license requirements to use PBI to consume reports...

P.

Highlighted
Frequent Visitor

Re: Reoccuring export of SharePoint List (Monthly snapshot)

Hi P, 

 

Thank you for your reply. 

 

I am aware of this option. However, as far as i know, connecting directly to the sharepoint list will give me a 'live' feed and overwrite the data as it is refreshed. I.e. it will not create a monthly snapshot so i can compare months. Hence, I was looking into excel as a way to record the snapshots of the sharepoint list. But perhaps there is other ways of doing this?

 

Kind regards, 

Jon

Highlighted
Power Participant
Power Participant

Re: Reoccuring export of SharePoint List (Monthly snapshot)

Hi @JJJ94,

 

So if you are looking for monthly shanpshot for live data in one SharePoint list I beleive the best option is to store it in another SharePoint list. This can be mirror of the first one with DateTime stamp added. Such list can be connected to PowerBI adn report generated.

It is similar like archive as in SharePoint online there is "plenty" of space for such data.

P.

Highlighted
Frequent Visitor

Re: Reoccuring export of SharePoint List (Monthly snapshot)

Hi P, 

 

I have tried this now but it is still very slow in getting the data. Perhaps my MS flow should be different? I have had to set pagination to 5000 to include all the rows, as I found that it only collects 100 rows as default.

 

Regards, 

 

Jon

Highlighted
Frequent Visitor

Re: Reoccuring export of SharePoint List (Monthly snapshot)

My solution was to use the sharepoint to CSV template in MS Flow and then connect to the sharepoint folder via Power BI. 

View solution in original post

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (7,490)