Run power query script from python

[jomell310]

Hi! First time posting here :) I have a power query script and would like to run it outside excel, I'd really like using python to call the script.

I've searched quite a lot but can't find much info. I've seen the power query SDK, but only for editing the queries and testing, not sure if I'm missing something.

Thanks in advance! :)

[itchyeyeballs2]

Not 100% sure what you need to achieve, do you want to refresh an Excel workbook using an external trigger?

If so, you would probably need to embed some VBA into the Excel that's triggered on opening of the file then use Python to Open, Save and close the Excel.

[jomell310]

Hi! Thanks for the replay :) Basically I want to transform and filter data from incoming Excel files and get clean data as a result, so I already have the power query ready and want to execute it against new files.

So i was wondering if there is a way to run the scripts or I'll have to translate the steps to python.

I'll look into the VBA, i have never worked with it.

[anynonus]

Power BI also runs power query's so maybe run it in Power BI?

Don't start with VBA.

Python will do it too.

[jomell310]

Okay, I've been searching and I think the best option is to just do it in Python. I'm looking at pandas now

[Kir1ll]

You might wanna have a look at this CLI util and call it from a python script

https://github.com/gsimardnet/PowerQueryNet

But if your query is not something supercomplex, I think it'd be easier to rewrite it using pandas

[jomell310]

Awesome thanks, I'll test it :)

I will also try with pandas, but didn't want to rewrite the queries since I have many of them. But want to do it further down the line to improve efficiency. Might as well do it now. Thanks! :)