•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Why do advanced users move from Power Query to Python for Excel automation?
Our take
As data workflows grow more complex, many advanced users find themselves transitioning from Power Query to Python for Excel automation. While Power Query excels in data cleaning, transformations, and merging tables, Python, especially with the pandas library, offers enhanced flexibility and control. This shift often stems from the need for advanced data manipulation, automation capabilities, and integration with other programming tools.
I've noticed that many data professionals recommend switching to Python (especially pandas) instead of relying only on Power Query when Excel workflows become more "serious" or complex.
From an Excel user's perspective, Power Query already handles cleaning, transformations, merging tables, and automation pretty well, so I'm trying to understand where Python actually becomes the better tool?
I am interested in real-world experiences and decision criteria especially from those who used both or one of them.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Do you use VBA regularly or have you moved entirely to Power Query and formulas?I have been using Excel for years and VBA was always my go to for automation. Lately I have been seeing more people say they barely touch VBA anymore because Power Query and dynamic arrays cover most of what they need. I still use VBA for things like automating reports across multiple files or generating custom email bodies from data. But I am wondering if I am behind the curve. For those of you who work in data heavy roles, what is your current workflow? Do you still use VBA regularly or have you replaced it with other tools? Curious if I should be spending more time learning Power Query and the newer formula features instead of maintaining my VBA skills. submitted by /u/ProfessionalOk4935 [link] [comments]
- Any point in using a no-code ETL tool to transform excel?I'm trying to understand whether I should be using a third party tool to do some data transformation to join/filter a few spreadsheets on a regular basis or simply try and learn Power Query (which I don't like to tbh, but maybe that's because I haven't used it enough). What do you guys think? Should I go all in Power Query for a while, or maybe explore some other tool (no code, preferably) to create a "workflow" and run them from time to time? Have you guys experienced anything similar to this? submitted by /u/Remote-Ad-6629 [link] [comments]
- Get more from Power Query in Excel with these little-known capabilitiesIf you use Excel or Power BI to work with data, you’ve likely encountered Power Query - and may already rely on it regularly across desktop and web. It pulls data together, cleans it up, and prepares it for analysis. But beyond those familiar tasks, Power Query includes several capabilities that can make solutions more flexible, scalable, and easier to maintain. Get more from Power Query in Excel with these little-known capabilities submitted by /u/beyphy [link] [comments]
- The difference between "a tool exists" and "automation"I worked in a marketing agency, and every Week we had to export excel files from Meta, Google, LinkedIn...etc. and we had some problems like : Meta calls it "Amount Spent", Google calls it "Cost" Meta exports in one date format, Google in another Meta has different row structure, Google has summary rows mixed in They never match cleanly Ever! So I had to learn Power Query, but it wasn't Convenient, So I switched to python I've learnt python and pandas for months, and Now I can comfortably AUTOMATE the cleaning process When I tell people don't waste your time you can automate that with python, I get answers like : "Ohh Power query is good ","power query worth learning","python is only for complex cleaning", DO YOU EVEN UNDERSTAND ENGLISH ? I SAID AUTOMATION ! AND PQ IS A CLEANING TOOL ! Power Query still requires you to: Manually download the exports every week Open the file refresh the query Check it looks right Copy into the report template Email it to the client But with Automation: Your script runs automatically on Friday morning. The branded PDF lands in the client's inbox before the agency owner wakes up! Python is not a Cleaning tool, it's Automation! it's hours saved, not a cleaning tool! We can't compare between them they don't do the same job! Just think of it like someone who refused to use a vacuum cleaner because he have a broom! Does it make sense ? NO! submitted by /u/ishaklekbir [link] [comments]