Forum Discussion
Customer's purchase frequency based on transactions table. Autonomous Store Frauds
As stated before: Check out Fabric. You can use a Power BI Gateway to connect to on-premise sources like your folder. Then use Power Query to extract data using the script(s) you have now. (You may even be able to copy and paste the M code with minimal manual editing!) And finally, you can push the Power Query Dataflow output to a table in a Lakehouse or Warehouse.
Honestly, though, I would also incorporate a Pipeline or two to process one file at a time, then move that file out of the way. (you really don't want to be processing hundreds of files via Power Query every night if all you are doing is adding a few more to the folder each day.)
Best of luck.
If you are pulling from a local flat file in OneDrive, yes, performance is going to be very slow, especially 1/2 million rows.
I suggest you look into Fabric. See if you can pre-process your flat file into something like a Data Warehouse via a Pipeline, Notebook, or Dataflow. Make this processing happen off-hours.
I am doing this already, PBI updates runs off-hours already. But I am not using these Fabric layers. The files (around 200 files now, growing everyday as time goes by. A couple of files each new day) are located in one PC and PBI uses the gateway to get the updates.
By any chance, Can I also find ways to optimize this gateway update routine and time consuming?