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.
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=x7q1DHf8wE4
Thank 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?
jeffshieldsdev I understand it is possible, but I am asking myself is it worth it. I would rather refresh one source instead of refreshing everything twice (Dataflow + Dataset). Seems like simpler approach. And Direct Query seems too slow to be considered the best option.