Power Query alternative

[justlogmein]

I have been using Power Query for about a year now and while I find it very useful, it has come to a point where I am spending 95% of my time just waiting for it to refresh. A file may have up to 30 queries and each up to 80 odd steps. When I make a change, it takes literally hours for it to run and I can't help but think if I wrote all those steps in another langage this could realistically be done in minutes if not seconds.

While I recognise that my code is not 100% optimised, all of our projects are complex like this and we can't rely on Power Query any more to do our ETL as it is just too slow. I am wondering what other ETL tools are out there that could do something similar to PQ, albiet without the UI?

[v-chenwuz-msft]

Hi @justlogmein,

If you know about Python language, you can use Python Scrpit for ETL in power query.

Using Python in Power BI Power Query Editor - Power BI | Microsoft Docs

But keep in mind that, only personal gateway support python script refresh in power bi service.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

Have you considered showing us how we can assist?

[justlogmein]

I have trimmed it down a bit, but here it is. The query I am having trouble with is the Single_Match one. You can see I have only a handful of steps but whenever I make a change it takes forever to run.

https://jmservicescomau-my.sharepoint.com/:f:/g/personal/jacob_jmservices_com_au/EvR6yqvf1mBJuHRV7Qq...

[edhans]

You have a lot of query merging with itself. I counted 16 Nested Join statements with only 4 queries. I'm not sure that is the best way to go about it. Maybe someone else can see a fast way out of it, but this seems more of a consulting project and not a one off question on how to do something. 

[watkinnc · Accepted solution]

My friend,

Before giving up on the wonderfully productive tool that is Power Query, i w courage you to consider the following:

Do you have 30 completely different data sources? If not, I bet you could be reusing a lot of the stuff you might be querying more than once.

Are you able to move your joins further up to before you lose query folding. Do you HAVE to sort stuff? Buffer stuff? Are you buffering your lists, especially ones used in functions? Are you using a bunch of "or Text.Contains"? Can you wait til the last steps and then reorder/change name/change types only once?

95% of your query might be optimized, but that's 5% of your ship with holes in it. If you are using 30 80-step queries, which itself makes it difficult to imagine such a query plan, but if so, it needs to be 100% optimized.

Power Query will give you what you want, you just have to know what to ask for.

I would be grateful if you could post some code for us to review.

--Nate

[edhans]

Power Query is the only tool I know of that will do the transformations and load the data into the model. Other tools like Alteryx are popular transformation tools, but they will simply stage the data as far as I know. Then you'd use Power Query to just open that data up and load it directly with nothing more perhaps than "Changed Data Type" steps.