The difference between "a tool exists" and "automation"
Our take
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!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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. submitted by /u/Isaac__dev [link] [comments]
- I want to use Power Query to import data received from a client, where the file name changes each month. What's the easiest way to automate this?I used to use VBA for this, but that's a lot more roundabout, and I have a lot less control over the transformation. I have no issues with transforming the actual data itself. My issue lies in the fact that it's a different file each month. Using wildcard formatting, *filehere*.xls* would always pull the correct file. This file is also stored in the same place relative to my spreadsheet each time, but the location of the spreadsheet and folders itself changes each month. In VBA, I could find the relative position quite easily via ThisWorkbook.Path & "\Data\" However, I don't know how to use PQ to import automatically like this, so that I'd always import the correct data simply by refreshing links. I think I've seen people set up a somewhat hacky way, where PQ first reads a table in the workbook to retrieve values, and then uses those to find the file to query. Is that the only way? submitted by /u/space_reserved [link] [comments]
- Summarize simple use of Power Query for impatient experienced user with simple need?I'm a longtime Excel user, comfortable in using it for all kinds of text manipulation and basic number crunching, beginning to get a handle on tables and new 365 formulas. I've learned most of what I know from self study and enjoy it, but at the moment I'm a bit overwhelmed with trying to get started with Power Query. It seems like just the tool for what I need to do, but it's also designed for a lot more, and I'm having trouble getting my bearings since even the basic introductions assume that my needs are much more complex than they are and talk about a lot of options that are irrelevant to me. This is all I need to do: I have a workbook with a table where I keep track of work on inquiries sent to an online platform from which I can export submissions into Excel. The export contains a few columns I don't need, and lacks a few others that I add in the workbook, so what I've been doing is opening the export file, deleting unneeded columns, adding blank rows to the bottom of the table, copying the data from the export file, and pasting it in beginning with column D. Seems like just the kind of thing to automate with Power Query. But I don't want to keep the export file anywhere once I've appended its contents to the workbook. The data remains in the online platform if I need it, and storing any subsets would only waste space and cause confusion. I want to export the latest submissions, transform them with Power Query (I've already figured out how to do that), append them to my workbook, disconnect the two files, and delete the export. Sounds simple enough. But I can't seem to find out how to do this very simple thing without having to sit through long explanations of things I don't need to do and am too busy to wrap my head around right now. Can anybody point me to some simple step-by-step directions that just tell me how to do only what I need to do without bothering with all the other possible scenarios? Or is Power Query overkill for my needs? Is there another alternative? Thanks. submitted by /u/fastauntie [link] [comments]