Answered

Reoccuring export of SharePoint List (Monthly snapshot)

(0) Share

Report

Posted on by JJJ94

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

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

vecerpa 786 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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-List-Library/td-p/280109

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

Was this reply helpful? Yes No
JJJ94 28 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
vecerpa 786 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
JJJ94 28 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

JJJ94 28 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No