cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JJJ94
Level: Powered On

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
JJJ94
Level: Powered On

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
Super User
Super User

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.

JJJ94
Level: Powered On

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

Super User
Super User

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.

JJJ94
Level: Powered On

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

JJJ94
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 473 members 5,675 guests
Please welcome our newest community members: