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.
Super User>>I am doing this already, PBI updates runs off-hours already. <<
No, you are not. It sounds like you are asking Power BI to run a refresh of 200 flat files through a Power Query that looks to be like 60 lines of Applied Steps, oh, and access those files via a Gateway.
What I am suggesting is that you figure out a way to process those files with some other tools so that the result is a fully processed dataset that is then consumed by Power BI.
The Folder source in Power BI is great, but it is horribly slow, probably made even more so by the 60 applied steps.
How many files to you expect to have in 6 months time? An additional 200?
You are eating soup with a fork and wondering why it is taking so long to finish a bowl.
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.- ToddChitt
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.
