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.
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.
I see. Some reasons for splitting dataset into dataset and dataflows:
- The queries in the dataset you want to leverage across multiple datasets.
- Ingest data once and then refine in additonal dataflows or the dataset--data pulls are not reused in Power BI Desktop, Reference Queries will pull the data again.
- Depending on data source, this could reduce consumption costs.
- You want seperate refresh frequencies--maybe some queries only need to be refreshed monthly, others daily or multiple times per day.