ETL POWER QUERY and Python

[Mederic]

Hello,
I would like to learn the Python language for Excel but I have a lot of questions, in particular about the comparison between the Python ETL tool and the Power Query tool.
It seems to me that Python's Pandas, Openpyxl and Xlwings libraries are useful for automating tasks like Power Query could do.

Is Python better than Power Query for ETL?
Do you have any tutorials or videos for learning Python for Excel ?

Thank you in advance for your answers and comments

Best regards

[Mederic]

Hello @Syndicate_Admin ,

Thanks for your message, you're right to ask this question.
I'd like to learn Python in the hope of doing everything you can do with VBA, Power Query and DAX.
For example, automatically generate an Excel file after :
    . combining several other .xlsx files.
    . modify certain columns
    . add new data or columns

Until then, Power Query can do it without any problem.

But in this file there is another tab which contains certain parameters useful for Power Query, such as the path to the folder, the sub-folder with the name of the month, which must be adapted each month, etc.

The final result of the file corresponds to accounting entries which must then be transferred automatically to SAP

The problem is that this file must not contain any other Excel sheets or formulas....
The current solution is to copy/paste into another .xlsx template file and then transfer it to SAP.

This is just one example. I know that Power Query doesn't do conditional formatting. So you have to use VBA, which I'm not very good at.
Python seems more powerful than Power Query and VBA and I was interested in learning it.
I've found quite a few other interesting links, I'll post them here if anyone's interested.

But with all that, I don't know where to start and the best way to install Python.
Should I use an Anaconda environment?
How do I set up this environment properly?
In short, it's going to take me 10 or 15 years to get my head around all this 😀.

https://www.youtube.com/@coreyms/videos
https://pandas.pydata.org/docs/
https://openpyxl.readthedocs.io/en/stable/tutorial.html
https://wesmckinney.com/book/
https://automatetheboringstuff.com/
https://realpython.com/

And I have 1 or 2 forums with a Python section

Sorry if my English isn't clear, I used deepl to do the translation

Best Regards

[v-xinruzhu-msft]

Hi @Mederic 

The difference between Python and Power query is depend on the situation, the power query is friendly for the people without coding and it can help connect and transform data. the Python a powerful programming language, it offer more options for the complex data processing,  you can learn the related knowledge on the youtube.You can refer to the following link.

Bing Videos

Best Regards!

Yolo Zhu

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

[Mederic]

Hello @v-xinruzhu-msft ,
Thank you for your reply and the link for the videos,
Power Query is indeed friendly.
I'm afraid that for very complex cases, you need to have a very good level of Python. It may take a long time to learn.
I don't know if there are any forums on Python but I'm going to look there too.

Have a good year
Best Regards

[Syndicate_Admin]

Hi, could you please give some examples of cases where you'd need to use Python instead of Power Query? I can't find anything concrete so far.