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.
Three Super User rank tiers have been launched!
Features releasing from October 2020 through March 2021
We are excited to announce the launch of Power Virtual Agents Community. Check it out now!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!