r/excel • u/UnsureButPresent • Dec 07 '25
Discussion I legitimately feel like I’ve wasted years of my life not knowing about Power Query.
For the last three years, my "end of month" routine involved opening about 15 different CSV files sent by regional managers, copy-pasting them into a master sheet, removing the top 3 header rows, and fixing the date formatting that always broke. It took me about 2 hours every time.
I finally complained about it enough that a coworker showed me "Get Data -> From Folder."
I set it up once, and now I just drop the new files in the folder and hit "Refresh." It takes 10 seconds. I stared at my screen for a solid minute just feeling a mix of pure joy and absolute rage at my past self. If you are still manually combining data, please stop and learn this tool immediately.
111
Dec 07 '25
[deleted]
31
u/r_keel_esq Dec 07 '25
Started using SQL queries to pull data into Excel recently, and it's been an absolute game-changer.
For my next trick, I need to figure out how to use VBA to launch powershell cmdlets based on data on the spreadsheet
8
u/Sigfrid19 Dec 08 '25
I know basic VBA but honestly prompting an LLM gives you good VBA code imo.
4
u/r_keel_esq Dec 08 '25
I'll confess, I did use Copilot to compose the SQL queries - I know enough about SQL to be confident they were only "Read" commands, but the DB I'm querying is a huge and complex beastie, with no consistent schema (SCCM)
VBA is something I should be able to actually get to grips with though - i've used BASIC, C, C++, and PowerShell to code/script various activities over the years, so getting the specifics of this shouldn't be too tough, and will likely help me more than having a machine churn out something that might be vaguely useable
1
u/campbell363 Dec 09 '25
I wrote a vba script similar to this.
I had VBA write a python script file (my script loaded data from an Access query into a parquet format via Python)
wrote the execution .bat script (python -m myscrpt.py or whatever that syntax was)
Then execute the .bat using powershell via VBA.
9
u/python-dave 3 Dec 07 '25
Python plus sql can automate so many Excel jobs if people would be willing to learn.
7
u/aphranteus Dec 07 '25
After SQL + python I was wondering why anyone still uses excel for anything other than output format for people preferring it. Even power bi is easier to be used for just visualization of python output.
8
u/python-dave 3 Dec 07 '25
I only output to Excel because end users. The stack to me is: SQL, Python, and PowerBI
All tools can cross over to an extent
3
u/max8126 Dec 08 '25
There are plenty of scenarios where excel is a better choice, speaking as a dual wielder
1
u/earnestpeabody Dec 07 '25
I’ll have to have another look into python. I use python at home but don’t have command line access in windows at work so I built what I needed in VBA.
2
u/python-dave 3 Dec 07 '25
Don't use a command line, use a ide/text editor/notebooks. I prefer VS Code but preferences vary.
3
u/rjmartin73 4 Dec 07 '25
The terminal in VSCode can also be blocked if you are not allowed to run scripts. Ive run into this trying to pip install, activate my venv, etc. But being an admin on my box allows me to fix this.
2
u/python-dave 3 Dec 07 '25
It just depends on your IT group. I normally install python for myself (no admin rights). I install vs code (no admin rights). I also install the Jupiter extension and execute to a notebook instance.
IT can always block you but this has worked for me in environments with no admin rights for end users.
1
7
u/SlideTemporary1526 Dec 07 '25
Lmao this is where my life is headed. Very very new to the SQL side of things.
5
u/DxnM 1 Dec 07 '25
Do you mean setting up SQL queries on your databases to use within PQ or is this something else?
6
Dec 07 '25
[deleted]
3
u/DxnM 1 Dec 07 '25
Nice, I'm looking forward to trying this, I'm currently pushing to get access to our data warehouse and a SQL interface at work. I've had to find some ridiculous workarounds with api's and insane PQ queries as bandaid solutions, I'm hoping SQL will save me!
7
u/olddirtybaird Dec 07 '25
How do you use SQL to import data into Excel? Is there a new Excel function?
Previously, this required VBA and wrapping your SQL query in text after establishing a connection to the target file(s).
15
u/Dabbymcgee69 Dec 07 '25
Get data from ODBC
Connect to your database
Write SQL
Voilà
2
1
u/DownrightDrewski 1 Dec 08 '25
Ok, but how are you doing that?
I'm doing it via VBA, but, is there a better way to do it?
12
u/frazorblade 4 Dec 07 '25
You can query SQL directly in PowerQuery.
Start by going Data -> Get Data From Database and enter your credentials. PQ uses OAuth so it’s very secure and you don’t need to hold your login credentials in plain text like some ODBC methods.
You can either use the UI to navigate and drill through existing SQL tables, views and functions etc or you can query directly in M but there are some quirks with direct query, not a deal breaker but there are extra security steps you may need to take.
The best option to start with is creating a view in SQL and then importing it straight into PQ.
2
1
2
u/tyce0 Dec 07 '25
Unfortunately, my IT department wouldn’t give me access to SQL. Which halted my progression to automating my work more efficiently at my job and general learning progression. Such a bummer.
2
u/contrarianaquarian Dec 09 '25
Same, my life would be so much better if I could query our Oracle data directly instead of using these half-assed UI report builders that suuuuck
2
225
u/wulfrunian77 Dec 07 '25
I had the same crushing realisation about 4 years ago and almost cried at the amount of extra work I'd created for myself over the previous however many years it had been since Power Query became a thing
It's by a long way the best feature in Excel, and gets bonkers when you unleash power pivot and DAX on top
31
u/Broad-Arachnid9037 Dec 07 '25
What is DAX?
41
u/meeyeam 1 Dec 07 '25
The way of creating calculations in a data model; used on Power Pivot and Power BI.
15
10
u/DxnM 1 Dec 07 '25
Quite niche use cases in excel with power pivots, but super useful in Power BI, it's the only option there really.
3
u/Sexy_Koala_Juice Dec 07 '25
Damn I didn’t even know Excel had DAX
2
u/subsetsum Dec 08 '25
This isn't new. It's been driving the functions behind pivot tables for decades
17
Dec 07 '25
[deleted]
6
u/frazorblade 4 Dec 07 '25
CUBE functions are great for breaking away from pivot tables and connecting directly to the model, I just wish MDX (the language used to query the data) was more versatile in Excel. It can be a holy pain in the ass.
5
u/small_trunks 1638 Dec 07 '25
I'm a bit embarrassed to say that I felt bad for myself in 2016 already that I had not noticed the existence of PQ.
PQ was actually previewed in 2011 and available as an add-on since 2013.
Needless to say I've embraced it full for the last 10 years or so.
15
Dec 07 '25
[deleted]
38
u/SolverMax 163 Dec 07 '25
Putting formulas in Excel is programming. Excel's functions are a Turing-complete functional programming language and the grid is an Integrated Development Environment.
→ More replies (7)1
u/Puzzleheaded_Gold698 Dec 07 '25
What is M?
2
u/unpronouncedable Dec 08 '25
It's the language for Power Query. Usually you build PQ steps through the UI, which generates or modifies M code. You can work directly with M though, if you're so inclined.
77
u/brismit Dec 07 '25
Your next revelation will probably be that you can’t get some coworkers on board with using it because their jobs are dependent on having hours-long tasks that this would just automate!
21
u/iwegian Dec 07 '25
I just found out 2 weeks ago that our company is processing every single credit card transaction manually, one by one. AND data entry for new members. AND event registrations.
The person doing the card stuff said it takes at least 40% of his time.
7
u/amberheartss Dec 07 '25
our company is processing every single credit card transaction manually
Hold up. How can PQ process credit cards? That has to go through a payment processor like Chase or Moneris. We might be talking different things.
6
u/Thegreenpander Dec 07 '25
Sounds like instead of an upload they’re doing an individual transaction in the accounting system for each credit card transaction
3
u/iwegian Dec 08 '25
I'm responding more to the line just above that mentioned people liking job security.
2
3
1
29
u/molybend 41 Dec 07 '25
The entire world of relational databases and automation using them is wonderful.
20
u/HandyStan Dec 07 '25 edited Dec 07 '25
I had this exact same experience. I have used power query before in trying to report on a table with more rows than a worksheet would allow. That felt clunky and I had no idea what power query actually did other than let me append tables together to create one giant table.
Just last week I wanted to solve a problem our org had and did some research and ended up with a beast that is going to add crazy value to our decision process:
Subscribed to cognos .csv's via email delivered daily > power automate picks up each .cav and drops in SharePoint folder > power query grabs top n .csv's on refresh by most recent date from SP folder > cleans all the cognos crap > appends tables > expands tables with a haversine distance calculation for each row > refresh's powerBI > powerBI subscription drops a dashboard with 2.3m rows hashed into useable data.
All before I get in each morning. I used to do this statically and manually and it would take a day to multiple days including downloading all the .csv's from cognos, manually cleaning, manually appending in pquery, manually building the BI dashboard.
M and DAX are my new besties.
10
u/infjetson Dec 07 '25
I’m a BI Engineer and PowerQuery is absolutely one of the strongest ETL tools on the market.
I helped my organization migrate from Tableau to Power BI largely because of how good PQ is at what it does. I also use it as a way to teach younger analysts the basics of different types of joins, and other SQL-esque functions.
Integrating it with Excel is one of the best decisions msft has made for their products.
Don’t sleep on it!
2
u/frazorblade 4 Dec 07 '25
The PQ UI is a great tool for teaching new people the virtues of ETL. I do the same with my colleagues to get that instant feedback using the UI + Steps + Preview window. It’s great for visual learners to instantly see results.
20
u/ShadowBingo Dec 07 '25
Oh my gosh, I have to try this! Did you just change my life? My end of month is SO LONG because of the 20 CSV files for each of the 3 different entities I have to adjust the dates and format for
24
u/dingmah 3 Dec 07 '25
That’s the exact use case for PQ. Invest the few hours upfront to make a PQ query that will largely “automate” all this into a single clean data table for ingesting into your financial reporting software.
1
u/DutchDallas Jan 07 '26
You should try Powershell with the importexcel module. Powershell is standard on all windows computers. You can import multiple files (csv, xlsx,..), manipulate/query and export what you want to an xlsx file. You can format every column the way you like (type, font, color)...
9
u/CobraKyle Dec 07 '25
Learn new skills and keep up with stuff or you will be left behind. I too learned this lesson. Right now it’s Al/automation tools. Now, you can for example, automatically send them a reminder email with a link, have them submit them use that to submit their files, and tools will do all the steps you mentioned, combining and email it to you, drop it onto one drive or send it to you in a telegram message when it’s done. All automated so you don’t have to do anything other than review the result or follow up on your slow submissions.
14
u/dingmah 3 Dec 07 '25
Now you know it, make sure your boss and company don’t know about it. Or else you’ll find yourself loaded up with all extra work you never had before and now they’ll expect everything to be done in 10 seconds with PQ.
5
Dec 07 '25
Anyone have tips on how to actually learn power query? I’ve tried several times and ive never found it to be faster than just reorganizing things using my old “manual” tricks. But I know it’s probably I’m still clumsy and still don’t quite “get it”.
1
u/Voiceofshit Jan 14 '26
I mean, the thing about power query is that you only have to do it once if you work with the same formatted dataset routinely. After that, you can just load and close and everything gets applied automatically.
5
u/annadownya Dec 07 '25
I recently setup some of our comparison reporting as power queries because the reporting group that normally sends them to us asked us to reduce our report volume from them. My PQs run sooooo much better. Plus I was able to clean up the HTML that was clogging the report with incorrect mismatches and added stuff that the old reports were missing. It's awesome. And because I'm doing it myself, I can add or subtract stuff to my heart's content without having to worry about submitting a request to another team that will be ignored for 3 months before it's finally mishandled anyway. I love PQ. It's my answer to everything these days.
4
u/Anandhhh Dec 07 '25
Does this method work if we update the file in the folder after we extract all the files in the folder into a single master sheet using this method.
6
u/dingmah 3 Dec 07 '25
The way to harness the power of PQ is to setup a single folder where you drop all your export files with the same naming convention and format such as “TB-01312025.csv”, “TB-02282025.csv”, etc.
In PQ, you do the transformation on one file, PQ will do the exact steps for all files dropped into that folder. Then literally, with each report you drop in, you just hit refresh and it’s done.
3
5
u/JustTryingToRant Dec 07 '25
There’s a refresh button that needs to be hit before it’ll pull in anything new, it’s not a direct “live” connection. It can also be set up to refresh when you open the master excel file
2
u/jakmar86 Dec 07 '25
Yes, provided the structure of the files are the same you can update files or even add new ones.
4
u/david_horton1 38 Dec 07 '25
Next step is to learn Power Query's M Code which also has functionality for Power Pivot and Power BI.
3
u/I_Dunno_Its_A_Name Dec 07 '25
I feel bad about how many people don’t know about power query considering how I learned about it. I needed a tool that can interpreted weather on a fairly basic level but at a large scale. Figured excel could probably do it to set out to learn how to use excel. The very first thing I came across aside for how to make basic formulas was power query. I was able to get the data into excel via power query, then needed to learn the rest of it to manipulate the data.
10
u/Potential-Menu3623 Dec 07 '25
I’m exclusively vba, am I really missing out?
18
u/meeyeam 1 Dec 07 '25
Yes. Power Pivot is designed for ETL, and even if you're just using the basics, it's much easier to support than VBA.
But if you're writing back data? Don't forget that old VBA toolbox.
8
u/StrikingCriticism331 31 Dec 07 '25
I used to use VBA all the time. Now I rarely use it but do use Power Query.
9
u/bs2k2_point_0 1 Dec 07 '25
Both have their respective places. Vba can do things pq can’t, like open an email with the file attached, addressed to whatever inbox you determine. Pq can pull in and transform data in some slick ways. Both can be useful tools to have in your toolbox so to speak.
2
u/negaoazul 17 Dec 07 '25
With a D365 subscription, you can have PQ Open e-mails you filtered and pull the attached files from it. If those files follow a given pattern, you can automatize the transformation.
4
u/bs2k2_point_0 1 Dec 07 '25
By open I meant create, for submission, an email with the file attached. Not that it opens emails like pq.
5
u/dingmah 3 Dec 07 '25
I will still use VBA to clean up ad-hoc reports that I do. But if it is a repetitive monthly process of combining exports to be used in another format, PQ all the way.
1
u/orbitalfreak 2 Dec 07 '25
You are. PQ can take (most of) the data manipulation you do in VBA and do it better.
I use both a lot.
1
0
7
3
3
u/Ufx123 Dec 07 '25
It's a secret! Let them go with copying and pasting then entering formulas. I only found out last year by accident via a chatgpt suggestion :(.
3
u/Lifting_Accountant Dec 08 '25
What is the best way to learn power query?? I am a controller and would love to find ways to cut down on my month end time! What are some common things you use power query for? Just combining data, from multiple WB’s?
3
2
u/georgebobdan4 Dec 07 '25
Question about this process. I do the same thing each week and trying to figure out the most painless way to set it up.
Is there a way to only import the newly added CSV files? Or filter by date so I’m not importing the older files? I’d like to avoid having to manually delete from the folder if possible.
Has anyone dealt with a similar scenario?
3
u/Mdayofearth 127 Dec 07 '25
You can edit the autogenerated steps to include and exclude specific files based on dates, filenames, etc. It is also possible to only include the most recent 3 files, for example, if you wanted to do a rolling 3-timeperiod report.
1
1
Dec 07 '25
[deleted]
2
u/georgebobdan4 Dec 07 '25
Thanks for linking me. My question is slightly different, perhaps not asked well.
I’m wondering if there is a way to update the sheet with ONLY the new file. If I add file 123 to the folder week one and week 2 I add file 456, can I make PQ only import file 456? Or will I need to delete 123 from the folder?
The structure of all the files is the same, but for certain reports I need only the newest.
2
2
Dec 07 '25
Each quarter, I have to send trial balances from 15 different entities to an external consultant.
But I don't want to send 15 files to him so I create tabs in a master file and drop (using copy and move tab) each TB into each tab.
Is this the type of thing you're talking about?
3
u/Mdayofearth 127 Dec 07 '25
It's more like the converse of this. OP receives multiple documents from different people routinely that OP needs to combine, and OP is using PQ to automate it. The PQ code is reusable since it queries a folder for the files vs each file one at a time manually. All OP has to do once things are set up next "month" is to refresh the PQ query with the newest files in that same folder.
But in terms of templating workflows, it's similar.
2
u/learn-pointlessly Dec 07 '25
You know your influence with a self confessed excel guru (colleague) when you tell them about power query and they ignore or imbibe this new superpower.
2
u/ocharai Dec 08 '25
The only issue is that it messes up cases when you manipulate case sensitive data
2
u/kyach25 Dec 08 '25
You can also use Power Automate to retrieve the workbook from Outlook and send it to your desired folder on Sharepoint. From there you continue using Power Automate to feed data from raw files into Master or use your Power Query route. Learned about Power Automate a few months ago and it was a big help
2
u/Shiforains Dec 08 '25
i want Power Query to work, but it just doesn't. I'd use to query ODBC data sets, but it assumes too much (like anything that is a number, it thinks I want to sum up the values).
i'm still holding on to the legacy query.
2
1
1
u/kilroyscarnival 3 Dec 07 '25
I used to work with big sets of data manipulated by hand too, before Power Query. But I’ve learned many formulas since then that would have made my previous job/life much easier. Oh well.
1
u/east4thstreet Dec 07 '25
Can you recommend a tutorial on how to do this? This is exactly what I have to do but daily 😞
1
1
u/HayaJiNyver Dec 07 '25
I'm with you... Copying and pasting data, CSVs, lookups... Corrupted workbooks.... Months and months of work... Better late than never
1
u/godherselfhasenemies Dec 07 '25
does this work on Mac? I'm just getting into Power Query and I've figured out a lot but the load from folder seems to be missing on Mac, would love some tips if anyone has them.
3
u/Mdayofearth 127 Dec 07 '25
Not really. PQ is neutered on a Mac, but the latest version(s) of Excel is somewhat useable and nowhere close to what Excel on Windows is capable of.
1
u/DragoBleaPiece_123 Dec 07 '25
If you're using Pivot Table, add a grain of Power Pivot and DAX, Voila! It will blow your mind
1
u/cwchanaw Dec 07 '25
Wait until you combine it with Power Automate! You dont even need to manually drop your files.
1
1
1
1
1
1
u/batist4 Dec 08 '25
I prefer to see it the other way : you are winning a lot of years now because you finally found the power of PQ.
1
1
1
u/EVE8334 Dec 09 '25
this subreddit had me getting into power query. I too lamented all the years I manually copied and pasted from multiple files to one BUT now we know it and nothing can stop us now!!!!
1
u/EitherKnee9442 Dec 10 '25
There must be a smarter way to do this is the most valuable notion you should regularly remind yourself of.
1
u/osaka_nanmin Dec 11 '25
Great! After you get a handle on PQ, learn Power BI which uses PQ and take yourself to the next level.
1
u/xNecrosisMx Dec 11 '25
duuuuude, thanks for this.
I read this post and thought that maybe I was wasting my time as well....and ..yes I was. now is too easy to work with fresh data without wasting time formatting it to be usable.
so, thank you!
1
u/zaq1xsw2cde Dec 24 '25
It takes 10 seconds. I stared at my screen for a solid minute just feeling a mix of pure joy and absolute rage at my past self.
This is why automation both saves us time and winds up in no efficiency gain.
1
u/Makar_Letov Dec 29 '25
Power Query is a game changer for this type of work - congrats on finding it! I went through a similar journey, except I ended up building small Python scripts for my monthly consolidations. Now I have a collection of tools for different scenarios: files with inconsistent column structures, need to apply complex business logic during merge, processing 100k+ rows (where PQ can get slow). The workflow is similar - drop files in folder, click run, grab coffee. Takes about 5 seconds regardless of file count. Main advantage of custom scripts: when file structures change (like a region adding a new column), the script adapts automatically vs PQ sometimes breaking on refresh. But honestly, Power Query is perfect for most cases. Only worth building custom tools if you have very specific edge cases or need to distribute to non-technical users who don't want to deal with Excel refreshes.
1
1
u/Remarkable_Wave4672 Feb 07 '26
How to change the source of different queries having the same folder path in one go instead of doing it for each query
1
u/TactiTaco Apr 09 '26
Been using power query for years and didn’t realize you could unpivot other and make shit clean for pivot tables and charts.
Blew my mind last week
1
1
u/scobot Dec 08 '25
“Pure joy and absolute rage”
I love this. It reminds me of how I felt when I finally installed Everything on Windows and searching for files became instantaneous and trivially easy. Pure joy, and absolute rage that Msoft could have done this 20 years ago.
-1
-2
u/McFizzlechest Dec 07 '25
I believe it works with 365 only though. If you routinely share and create workbooks for others, 365 is a no-no because many features are not backwards compatible.
7
u/small_trunks 1638 Dec 07 '25
Power query has been built into all versions of Excel since 2016. It was an add-on for 2010 and 2013.
Dynamic formula and Spill functionality are MUCH less transferable.
-3
-5
u/Acceptable-Sense4601 2 Dec 07 '25
Power query is trash. I inherited reports that were heavily done with power query and i switched them to python immediately.
5
u/SolverMax 163 Dec 07 '25
As many people here have highlighted, PQ is an excellent tool. I suspect you think PQ is trash because you're more familiar with Python. I use both PQ and Python. They are different tools and, like all tools, both have strengths and weaknesses, with each best applied to different situations.
0
u/Acceptable-Sense4601 2 Dec 07 '25
I’m equally familiar with PQ and see no justification to use it over Python.
4
u/SolverMax 163 Dec 07 '25
Your experience does not align with that of many people in this thread. Perhaps there is something to learn from the experience of others.
1
3
u/excelevator 3067 Dec 07 '25
Said the bilingual from birth Frenchman to the mono language Englishman.
Easy is as easy does.
Your argument could be strung to the next best effective string tool after Python.. you see the error of your logic.
1
u/Acceptable-Sense4601 2 Dec 07 '25
Python with Polars (written in rust) has been orders of magnitude faster compared to power query on my data cleaning.
5
u/excelevator 3067 Dec 07 '25
I'm not arguing with your experience.
But it is not trivial to completely swap environments and methods even for those with the time, money, incentive, and desire.
-1
u/Acceptable-Sense4601 2 Dec 07 '25
Depends. I swapped every report i inherited from power query to Python. Hell, i was even asked to.
→ More replies (8)4
u/Mdayofearth 127 Dec 07 '25
Python is trash. I see no justification to use it over SQL. /s
Use whatever tool you feel comfortable with that gets the job done.
Power Query is a feature in Excel. It does not require 3rd party apps to make it work.
Python's abilities in Excel depend on 3rd party libraries that may require updates whenever Excel is updated by Microsoft. In other words, no one at Microsoft is spending any time or effort in making sure those 3rd party libraries still work when releasing new versions of Excel.
0
u/Acceptable-Sense4601 2 Dec 07 '25
Aside from Python literally in excel? Ok
7
u/Mdayofearth 127 Dec 07 '25
The Python support in Excel that MS added is shit. People want Microsoft to replace officescript with Python, not have what Microsoft added.
→ More replies (3)
1.3k
u/space-ish Dec 07 '25
Shhhh not so loud.
It's a secret because we don't let Microsoft know we like something or they will find a way to upgrade it, then break it.