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.
Thanks for the comments. You are right I need something more sophisticated to deal with the files. Not sure how or the tools to use to pre-process the files, specially for doing some data calculations as my 60 steps are doing it. I will try to learn more about Fabric.
Anyway I was expecting here to find ways to optimize the steps., speacialy the one calculating customer last purchase date.
Super UserAs 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.