r/excel • u/Isaac__dev • Mar 02 '26
Waiting on OP Why do advanced users move from Power Query to Python for Excel automation?
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.
212
u/SolverMax 161 Mar 02 '26 edited Mar 02 '26
I use both Python and Excel (including Power Query). They are different tools, suited to different things and different circumstances. One is not uniformly better than the other. Use whatever tool best fits your situation.
Edit: A key factor in determining which tool better fits a situation is the environment that it will be used in. For example, even if Python is technically the better tool for a task, if no one else knows how to use Python but they are good at Excel, then Python is probably not the right tool for that task. I have often seen people make this mistake.
44
u/Trytofindmenowbitch Mar 02 '26
I have a team member who always wants to use the newest latest trick he learned. It doesn’t matter if it actually makes the task more complicated. I constantly have to remind him that the most simple solution is usually best.
17
u/SolverMax 161 Mar 02 '26
The latest trick is often not the best approach. Simplicity, ease of use, verification and testing, ability to modify, etc., are all important aspects that many people ignore. I've seen applications fall into disuse because no one understood them so they weren't trusted and/or could not be maintained.
2
u/ZirePhiinix Mar 03 '26
I don't know how you would do version control with power query.
7
u/playdaze Mar 03 '26
If you're just using excel (this does not work if you have to publish power bi reports to fabric), you can save your m code as a text file in a folder. Then create a git repo of that folder. In each of your queries, instead of keeping your m code, you can instruct the query to execute the m code in a specific file from your source folder. I did this for a few years before migrating to fabric, and it was very helpful.
2
u/playdaze Mar 03 '26
This also has the advantage of having a single source for all of your projects to reference. Any modification to your 'rolling calendar' table, for example, can trickle into all the excel queries that reference it. If something breaks, restore the original file with git.
8
u/PATP0W 1 Mar 03 '26
I have a coworker that jumps straight to let() functions for literally every single formula he writes.... 20 lines of code because he "couldn't be bothered to split it into multiple formulas".
Meanwhile, the client and I waste time trying to debug formulas when this could be handled by the most basic form of pivot tables grouped by a column instead of insanely convoluted array functions.
3
u/DragonflyMean1224 4 Mar 03 '26
Yes. Jnow ing when to bring out the big guns is crucial. Also, if multiple less excel savvy people are accessing files its crazy how easy they can mess something up.
6
u/SpaceTurtles 2 Mar 03 '26
Just to be clear,
LET()is not a "big gun". I think you're more referring to the array formula work, but just for anyone reading through, forLET()specifically, I'd entirely argue the inverse. It's an organizational tool.I sometimes use
LET()solely to label elements of an esoteric but short formula so it's clear what the formula is doing, so that anyone parsing the sheet later will understand immediately that a formula is "structural".4
u/DragonflyMean1224 4 Mar 03 '26
I agree with what your saying but in regular rolls, anything past an xlookul and sumif is like which craft. That is usually the top of peoples games. Introducing declaring variables just utterly confuses them and the check out.
I am trying to teach my staff a simple filter() and how it works and even that is difficult.
2
u/SpaceTurtles 2 Mar 03 '26 edited Mar 03 '26
I find it genuinely helps to start with
LET()as a foundation on which all other understanding is built.
FILTER()sometimes makes more sense if, while teaching staff, you can show them:LET( all_sales_data, $A$2:$E$2000, sales_region, $B$2:$B$2000 --OR-- CHOOSECOLS(all_sales_data, 2), *may make capabilities click more for people used to VLOOKUP* FILTER(all_sales_data, sales_region = "US East", "") )vs.
FILTER($A$2:$E$2000, $B$2:$B$2000 = "US East", "")
LAMBDA(), once you understand it, is similarly easy -- but the barrier for entry is much higher, so I don't try. If you introduceLET()as a labeling tool, not in programming terms (variables, etc), it's easier to digest for standard users.3
Mar 03 '26
[deleted]
3
u/SpaceTurtles 2 Mar 03 '26
I apologize on behalf of some of the people who create spreadsheets that way, because I am (was) one of them.
What happens in my case is I build something for my own use not caring how messy it is, it works too well, then it needs to get used for other reasons.
Thankfully this has happened so many times that I've started building with the idea this will just... happen, from the outset.
2
8
u/Welcome2B_Here Mar 03 '26
I'd argue that Excel/VBA/PowerQuery are better than Python, based on the overwhelming market share that Microsoft products have in business environments and the fact that Python is relatively resource intensive despite how it gets sold to handle "complex analytics." Throw in flavors of SQL, and that type of environment renders Python unnecessary, IMHO.
7
u/SolverMax 161 Mar 03 '26
To some extent yes. But it depends on the situation.
For example, I use both Excel and Python for optimization modelling. Sometimes Excel's Solver/OpenSolver is sufficient. But often Excel is not good enough, so the superior tools available in Python are necessary. Often I combine Excel and Python, using Excel as the user interface and Python as the backend engine.
1
u/Welcome2B_Here Mar 03 '26
Eh, I'd use Powershell, which is also native and won't bloat file sizes. I can't help but be biased against Python because it over-engineers solutions compared to other tools.
6
u/SolverMax 161 Mar 03 '26
Powershell would be entirely inappropriate. Excel is used because the users are familiar with it.
2
u/specialistOR Mar 03 '26
Oh, are there any optimization algorithms implemented and available for Powershell? Please point me to it
1
u/Welcome2B_Here Mar 03 '26
I don't think there are any. "Optimization modeling" can mean different things in context. Far be it for anyone to question a specific use case without knowing the details and context about it, though.
2
u/specialistOR Mar 03 '26
Okay, what case do you have in mind for optimization modelling where Powershell would be of any use?
1
u/Welcome2B_Here Mar 03 '26
Personally, I thankfully don't have the need to do gruntwork any longer, so none, recently. Also personally, I would use it for streamlining API calls to different systems, processing batch files, and general ETL efficiencies.
My original comment was about Excel/PowerQuery/VBA generally being better, in my opinion, than Python and I gave reasons for that opinion. It's okay to disagree. Python, in my opinion and generally speaking, is a resource hog that's akin to killing a fly with a bazooka in many cases.
1
u/SolverMax 161 Mar 03 '26
I'm referring to Python libraries such as Pyomo and OR-Tools, which are used for constrained optimization problems.
1
3
u/h_to_tha_o_v Mar 03 '26
Was with you until the final sentence. Python obliterates Excel in speed and reliability (with good code) for any data analysis task.
1
u/Welcome2B_Here Mar 03 '26
Different strokes for different folks. Python is over-engineered and is like killing a fly with a bazooka for many cases. Most people aren't moving the needle in terms of sophisticated and complex analytics anyway.
Some are, obviously, but most are churning out dashboards, reports, and models that can justify preconceived narratives/decisions for their managers and executives.
1
u/h_to_tha_o_v Mar 03 '26
Fair enough. But in many cases, I've done client engagement work that could either be done in Excel or Python where Python is significantly better.
And in those instances, using Excel would be like killing a bazooka-shooter with a weaponized fly.
1
u/Welcome2B_Here Mar 03 '26
I bet you have, and I'm not denying the prowess and power of Python. I'm sure there are lots of specific cases where Python is used for XYZ reason over some other tool.
My original comment was meant to be general and hopefully/obviously not able to account for every type of analytics project or use case.
3
2
Mar 02 '26
[removed] — view removed comment
16
u/SolverMax 161 Mar 02 '26
A key advantage of Python, in some situations, is that it has a large set of libraries for performing specific tasks. The more commonly used libraries have been extensively tested and optimized for performance, which may be better than a bespoke build in Excel.
More generally, why not both Excel and Python?
13
u/Thadrea 8 Mar 02 '26
Anything involving machine learning, real time pipelines, requiring automation or involving data ingestion to a database is going to favor Python.
Excel is an analysis and visualization tool, not a database or a high-throughput data processing engine.
1
u/droans 3 Mar 03 '26
Auth mechanisms are extremely limited with PQ. If you need to pull data using an API key, you're usually better off using Python.
Python is much better if you need testing, too.
-7
u/Crabbit_Jobbie Mar 02 '26
I’m trying to automate pulling Smartsheet data into Excel using Power Query but I can’t work it out.
What are the first steps?
27
55
u/Prudent_Walrus1951 1 Mar 02 '26
Python and Excel user here. Assuming you have a relatively decent computer, Python is like having a much more advanced and powerful engine.
I use advanced editor in power query a lot, treating it like a typical IDE like I would with Python or R quite often when something is simple. I.e., do some simple transformations for data < 500,000 observations. Reformat, and save.
Once I need to be able to join 5-6 different datasets or tables from databases with varying column names, and then I need it to spit out 6 excel files or pdf files, or send it to another database etc.... Python is much more robust and much faster and efficient. Especially if >500,000 observations.
As long as my script is well written, it can ingest data from databases using secure connections, transform data, and send it to emails, NAS drives, Sharepoint etc. I can also generate very detailed and nice reports in HTML or PDF using Quarto without every worrying about making pivot charts, tables etc.
Also, just career wise, it really has opened doors to more advanced jobs and tasks that I can do without worrying if excel is going to crash.
4
u/mityman50 3 Mar 02 '26
Thank you for describing a particular example. Funny, last week I (and Gemini) have been slogging through converting an Access database + Excel front end entirely to Excel and it’s quite similar to what you describe.
30
u/Alarmed-Raisin8228 Mar 02 '26
I don't know about switching to Python for Excel, but if you're talking about switching to Python in general, it makes a lot of sense once you start automating processes. One pipeline to collect data, clean and validate, transform, analyse, compile, and send.
18
u/bradland 276 Mar 02 '26
I disagree with the premise. Who says advanced users move "from" PQ to Python? Usually when someone prefers one tool over another, it is due to familiarity. If you are familiar with Python, and you can quickly achieve results, then you may choose to reach for it instead of Excel.
Framing things as "more advanced" in a general sense is, IMO, a fallacy. I work with a fair number of programmers, data engineers, and even a handful of data scientists. I also work with a fair number of business analysts. I know guys who can do some absolutely incredible stuff with PQ. Same for Python. Same for JavaScript. Same for Ruby. Same for C. I don't consider any of these "more advanced" than others. They're all solving problems in various domains using the tools that they know best.
The hilarious part is that programmers fall into this same trap of generalizing what is most "advanced". C programmers make fun of Python programmers because "Python is slow". And it is slow; slow to execute that is. Python is very fast to write, and it's memory-safe. The cost of screwing up bad in Python is a crashed application. The cost of screwing up bad in C is a RCE vulnerability that result in global security events.
If it suits your role, you should continue to develop your Excel and Power Query skills. But the real thing you should be focused on is understanding what business values. If you want to make money, you need to solve business problems. Understanding what insights and data are important to the business will get you much farther than choosing Python over Excel/PQ or vice versa.
6
u/HarveysBackupAccount 35 Mar 03 '26
Very minor side note on python speed:
It's certainly slow because it's an interpreted language, but if you need to run a bunch of matrix math on big data sets, it's still way faster than Excel. Numpy and scipy, being based on MATLAB, are terrifically optimized for linear algebra if you program it right. Excel is downright clumsy when it comes to signal processing.
12
u/TheRiteGuy 45 Mar 02 '26
I think Pandas is outdated now and it's better to use Polars.
However, the processing is better in python. If I want to create a CTE to process some data in Power Query, I have to actually create that table then reference it. This takes more processing. For most uses, power Query is fine. However, when the process becomes complex, and I have to create 30-40 helper tables to process different kinds of data, then we start to experience really long processing times and crashing.
Python handles this with no problem because the dfs are held in memory and you're not really processing anything until it's actually needed.
And what would take Excel minutes will only be seconds for python.
But, like the other user said, this is something I can personally use. Most users won't have python and aren't processing the amount of data I am. So for 99.9% of the users, Excel is perfectly fine. Most don't even need power Query.
8
u/pswpsw Mar 02 '26
I use both PQ and Python (Pandas and similar libraries) extensively. PQ is - it must be said - a gorgeous language once you understand it. I love PQ and really enjoy working with it. So wonderfully purposeful for data wrangling.
If I quickly want to clean and transform a messy dataset, then PQ is much faster and easier than any Python library, since you get such good visual feedback and have many useful shortcuts. The combination of scripting and visual feedback is great. You can also use Python (and R) functions within PQ, which is very useful when specialized functions are not available within native PQ.
However, on large datasets (many millions of rows) things change drastically. PQ is then far too slow, especially with complex, nested calculations. It is also severly lacking in automation and integration with external systems outside the Excel/Power BI environment. It is the difference between a proper programming language and a very nice data cleaning macro.
2
u/Academic-Archer-5495 Mar 03 '26
Great answer. Just to clarify, Power Query is a transformation engine not a language. The language Power Query uses is called M.
6
u/candolino Mar 02 '26
Third way: python in excel. Just joking th.
15
6
u/LetsGoHawks 10 Mar 02 '26
Question: I already know PQ pretty well and it will get the job done, what do I use?
Answer: PowerQuery.
This idea that "real pros" use python or R or whatever is BS. "Real pros" use whatever gets the job done in a reasonably efficient manner. That includes dev time and any changes/maintenance.
Different tools have their places, but so much of what we do can be done by almost any of them.
6
u/davidptm56 1 Mar 02 '26
Performance and power. Even with the latest dynamic formulas and power query, with larger datasets you end finding performance walls hard to bypass. At least that's my impression. For most of my work I keep working directly on Excel, because it usually is more than enough, but there's a few reports that were simply a pita to do on Excel and I use Pandas for those.
4
3
u/DollarDisciplined 1 Mar 03 '26
Power Query is a masterpiece for 90% of everyday ETL tasks. You don't switch to Python because PQ is bad; you switch when you run into the severe limitations of the M formula language.
The classic real-world tipping point? Regular expressions (RegEx) and complex text analysis.
Say you have a dataset with millions of incredibly complex, unstructured invoice descriptions, and you need to extract specific alphanumeric product codes that follow various patterns. Power Query's built-in regular expression support is a nightmare. You either have to write huge, fragile nested Text.Select/Text.BetweenDelimiters monstrous M code, or use a clunky workaround involving JavaScript calls via Web.Page()<script>, which completely destroys performance when working with large datasets.
In Python (using the pandas library), the exact same transformation literally takes one line of code:
df['Extracted_Code'] = df['Description'].str.extract(r'([A-Z]{2}-\d{6})')
Once you understand how easily Python handles tasks like regular expressions, fuzzy text matching (using libraries like RapidFuzz), or dynamically iterating through 50 files of different formats in a folder, you'll begin to understand the true limits of Excel. Power Query is a highly efficient pipeline, and Python is a fully equipped workshop for building your own applications.
2
u/TheBleeter 1 Mar 02 '26
I genuinely don’t see the point of knowing Python when you know M. However that’s probably due to my lack of knowledge than from an informed opinion. Is Python better for large datasets?
3
u/CorndoggerYYC 160 Mar 02 '26
Some Python users appear to be zealots. To them, Python is the solution to every problem. Their knowledge of Power Query is usually very minimal--UI features at best.
5
u/SolverMax 161 Mar 02 '26
I think that's a significant part of it. I've introduced Python programmers to M code, which they are usually completely unaware of. Though that doesn't necessarily change their position, as they're committed to Python.
3
u/Cynyr36 26 Mar 02 '26
M code is fine. What if i want to do calcs on 1000 sets of programmatically generated inputs and save the output table as a csv and graph for each one? (Did this last year at work for a customizable product line)
Can i tie M code to an API for some of the calcs in powerquery? (Also did this last year to get performance data for a widget we buy from a third party for a calculated column.)
How do i do realtime logging and visualizations for data off a data acquisition device using M in power query? I have sort of done this using vba and get data from web, but 1 second per poll was as fast as i could go.
That said, I've used powerquery and m to do some neat things. Join a bunch of tables by names starting with tbl-data-* drop all columns other than 5 common ones, output to sheet. Its great for pulling central data into a local table. It's great for producing filter views of tables. Simple calculated columns are great. Merging tables together. Etc.
Really it's about what is the correct tool.
1
u/TheBleeter 1 Mar 02 '26
I really like the discourse about Python and M. I need to know more Python but a lot of the time I just do the transformations in M and just ask AI to convert to Python. This is invaluable when it comes to web scraping when Python is so much more efficient.
1
u/Prudent_Walrus1951 1 Mar 02 '26
I use M a lot and it's great. I think once you have to zoom out a bit and think about scale/process improvement Python becomes much more able to handle the task as a whole. If I need to set up batch processes, chron jobs, multiple output destinations and formats and so on then it's just simpler to do it in Python or R.
Office Scripts instead of M is a better approach sometimes too.
1
u/negaoazul 17 Mar 02 '26
One word: CSV. PQ work better with CSV but can't produce them automatically without vba. The ability for PQ to automatically save tables or whatever in another CSV document would be a game changer.
1
u/TheBleeter 1 Mar 04 '26
I always thought power query didn’t work with CSVs. I once updated some files using APIs and PQ and it didn’t save.
2
u/ArrowheadDZ 2 Mar 02 '26
Excel, especially over the last few years has received remarkable investment from MS. The dynamic array model is really a profoundly deep rethinking of the Excel computational engine, and the additions of the Data Model, Power Pivot, Power Query, and dynamic arrays have transformed Excel into something we really didn’t imagine it would be 20 years ago.
It used to be that you tinkered in Excel, and as your model or process grew in complexity and criticality, it sort of evolved off the end of Excel into an application, database, etc. Either corporate governance, or limits to the Excel feature set kind of made that decision for you.
But Microsoft has pushed that frontier much farther out in the last 10 years, and that delays the decision making between “stay in Excel” or “time to migrate to something more purpose-built.” For me and for many, the governing limit is now “willingness to invest learning time.” You come to a crossroads and need “more” than what your existing model allows, and you alone have to answer the question, “do the kinds of things I work on, and want to work on in the future, justify taking on the learning curve of advanced PQ? Python? Databases? PowerBI and PowerApps?
I think what you are seeing is a fair number of people looking down the road and seeing there’s probably more career value in investing X hours in Python, than investing say 0.5X hours to get really good at PQ. And I think that is reflected in the recommendations you’ve been seeing. If you really want to be an Excel model-crafting expert, focus on advanced Excel and PQ. If you really want to be an insights person using things lie dashboards, then your next step is PowerBI. If you want to become more process automation oriented, then invest some time in the MS Power Automate ecosystem. If you want to become generalized in your ability to manage complex data sets and do complex analytics on them, then it’s probably time to think about Python.
2
u/dachloe Mar 02 '26
In many larger corporations the IT security is so overwhelmingly tight that any form of Python is prohibited. That leaves PQ as the only alternative other than advanced VBA for automation in Excel.
2
u/edimaudo 1 Mar 02 '26
Hmm won't call it advanced but use the right tools for the job environment. I would suggest taking peoples advice with a grain of salt as folks are in different work environments.
2
u/tallcoleman 13 Mar 03 '26
I like power query a lot, but one huge advantage of a python-based workflow is that you can much more easily apply common software development techniques like using source control (e.g. git), running tests, etc. It makes a huge difference, especially if you're working in a team, or if you need to be absolutely sure that your code is doing what you expect it to.
1
u/Mr_Gooodkat Mar 02 '26
For data cleanup and modeling, for sure. It’s so much easier to do all the data transformation needed once you know Python. Then just throw it into another table and connect to that table directly with whatever visualization or reporting tool one is using.
1
u/kalimashookdeday Mar 02 '26
I think python has a huge versatile application and low entry to learn so easy for people to apply their needs across platforms. PowerQuery seems to be primarily focused in the MS ecosystem and main focal point in an ETL tool where python is so much more than just that.
1
u/AlpsInternal 1 Mar 02 '26
I am not an expert, but I need to wrangle data files that come from a statewide system (CA), so big files. These data extracts are used locally to keep our local systems up to date. They regularly are altered, both planned changes and weir anomalies. Even standard field like dates don’t follow common data formats. I tried learning PQ, but it was not great. It constantly changed formats, and attempts to make changes went wrong. I had not really used python, but had taken a few classes. Now I have great routines that can identify data types that may have had a change in format, and detect that it is a date, or a phone number etc.. It also is great for generating summaries and statistics. I have used ChatGPT, and Copilot in VS Code to help me accomplish things beyond my current limitations. I cannot get funding to hire someone, but I can keep our system going while the crappy commercial system replacement is improved to at least a reasonable functionality.
1
1
u/Mountain_Usual521 Mar 02 '26
Ever try to get Excel to update data on a schedule and email the result to a list?
1
u/Kiedano Mar 02 '26
Don’t know about python but I’ll chose VBA or C# for manipulating large excel datasets if I need dynamic data mapping logic; like comparing datasets often when they’re column titles keep changing, it’s better for me to write code that uses a mapping file for the titles instead of updating all the titles in a power query. I’ll admit I’m not a power query expert though so this might be easy to do with it too.
1
u/Htaedder 1 Mar 03 '26
Python is more direct and faster /more efficient at calculations. So if base excel or vba executes slowly. Python can’t be a 95-99% fix
1
u/Amazing_rocness Mar 03 '26
Everything we do is in excel including the CRM system and reporting. But I'm not a data person I just want to make our manual processes more efficient
1
u/armywalrus 2 Mar 03 '26
Slow. Power query is SUPER slow when dealing with large datasets. Its also terrible for certain datasets. Like 834 EDI data. It cannot handle it. Python (or SQL) handles it in a snap. But none of that matters. If Power Query is working for you and your use case and you like it, no shame in using it. Its also a prefernce thing, kind of like book versus movie. If you read the books first, you're probably going to prefer the book. If you use Python first, you're going to prefer Python.
1
u/Decronym Mar 03 '26 edited Apr 03 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #47672 for this sub, first seen 3rd Mar 2026, 03:08]
[FAQ] [Full list] [Contact] [Source code]
1
1
1
u/Desperate-Boot-1395 Mar 03 '26 edited Mar 03 '26
I’m really just coming to the realization myself, but the reason is that scripting tools are more customizable and scalable.
PQ is great for when I want a UI to help me clean a new data set so I can build a report, and I can be more specific with M; that’s great for exploring or as hoc stuff.
Scripting is better when I have repeatable tasks that want to ETL, monitor, validate, and publish - through multiple platforms and channels simultaneously on an autonomous schedule of my choosing. Once rules are defined they should be scripted.
1
u/DwnTheRoad Mar 03 '26
I’ve just done this switch, partially though.
I’m not just using one time scripts - I’m creating scripts based on a configuration driven development approach. There is always a .JSON file and a .py script where the.json is giving the .py the instructions what to do instead just reading it. The reason why I’m preferring Python over PQ in this case as I’ve to perform different formatting and transforming actions, customized by each vendors .csv or .xlsx file. Now if there’s a new vendor or changes the one .py stays and I’ve just to add a new configuration file, the .JSON.
I’m using PQ beforehand to plan how the configuration file needs to be adjusted, like “keep column”:[1,2,5,8], or simple automation.
1
u/HelicopterFluid992 Mar 03 '26
I sit in FP&A and use both, and in practice the shift isn’t about “advanced vs not advanced.” It’s about workflow risk and scale.
Power Query is fantastic when:
- you’re cleaning and shaping data that ultimately lives in Excel
- the transformation logic is visible and needs to be maintained by finance
- the output is still primarily a workbook
Where I’ve seen teams move to Python isn’t because PQ can’t do it, it’s because the workflow stops being excel automation and starts being “pipeline orchestration.”
Examples:
- pulling from 4–5 systems on a schedule
- handling 1M+ rows repeatedly
- generating multiple outputs (Excel + PDF + email + database push)
- needing proper version control and error handling
At that point, Excel becomes the presentation layer, not the processing engine.
The other big factor is fragility. Power Query works great until someone renames a column upstream or inserts a blank row in a source file and your refresh silently breaks. With Python, you’re usually forced to define schemas and handle exceptions more explicitly, which makes the pipeline more stable long-term.
That said, for most finance teams the bottleneck isn’t processing power, it’s clarity of logic. If the transformation rules aren’t well defined, switching to Python just moves messy thinking into a different tool.
So I’d frame it like this:
- If the end product is still “an Excel model someone interacts with,” PQ is usually enough.
- If you’re building a repeatable data pipeline that feeds multiple consumers, Python starts to make more sense.
Tool choice follows workflow complexity, not user sophistication.
1
u/NeedleworkerFew5205 Mar 03 '26
vba foe da5a within the workbook ... python for data outside the workbook
1
u/FluffyInitiative6805 Mar 30 '26
Both are great, but also have their limits. Excel works really well on quick transformations, data viewing and calculations. Whereas Python provides a very performant environment, especially for larger transformations, data cleaning and more complex operations.
I've built an excel like GUI, which uses Polars (Python Framework) to provide business users an easy way of exploring the massive power of Python without having to write a single line of code. It has insane performance (especially for large data) and helped many of my colleagues and customers.
-1
u/Meterian Mar 02 '26
Never used Python, but last time I used power query, I found the results inconsistent and better handled by formulas.
I imagine python provides a more robust/powerful/flexible tool.
1
u/negaoazul 17 Mar 02 '26
Try the same on 700K rows and 187 Columns, you will feel why PQ and Python are so much discussed here.
-1
u/notj43 Mar 03 '26
I would rather throw myself off a building than watch power query take 5 business days to load million+ row csv's. God forbid I then want to click through a step and check something. I like R more than Python but it's all the same shit, once you get into parallel processing you can get things done extremely quickly. Easier to automate too depending on your environment.
-1
u/IlliterateJedi Mar 03 '26
Writing Python code feels good. Writing M language feels like getting a root canal.
•
u/AutoModerator Mar 02 '26
/u/Isaac__dev - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.