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.
Solved! Go to Solution.
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...
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?
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.
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.
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!