Forum Discussion
Best Option for Power BI Data Repository
There are definitely plenty of ways to store data. If you are currently building reports off of a bunch of Excel files on your local hard drive, then probably the simplest approach is to save those same Excel files to the cloud (e.g. SharePoint) instead so you don't need to worry about data gateways or converting Excel to SQL tables. Not ideal for huge amounts of data (maybe setting up intermediate Power BI dataflows would help some?) but should be fine for lighter loads.
A couple points from my experience: Any kind of flat file store isn't going to have query folding--the Power Query engine will have to read the whole file to load in the data, which can be slow and run into timeouts.
SharePoint file storage can run into throttling, trying to ingest somewhere after 100 files, SharePoint will throttle your connection and your refresh will fail.
IMO a database is best: Azure SQL DB if you can get it provisioned and pay for it; on-prem SQL-based if you have an always-on computer to host the gateway.
jeffshieldsdev Can't I use the incremental refresh ability to pull in only the necessary files/workbooks I need to refresh from SharePoint? This way I would not have to refresh the 300+ workbooks I am appending into one dataset (similar to what query folding does... I think).
If your data is partitioned that way, I think you can tweak the M code to use the RangeStart and RangeEnd parameters to control which folders/files you're reading from.
EDIT: similar approach https://www.youtube.com/watch?v=x7q1DHf8wE4Thank you jeffshieldsdev . I can see this being very useful, however I would still need to refresh the dataflow AND the dataset if using Import instead of Direct Query within my report. That being the case, I would probably need to set up Power Automate flows to refresh the dataset after the dataflow is done refreshing. Can you confirm this or see a better way of going about using dataflows within a report?