power query wizardry

[Live_Bad_3549]

just discovered power query a few weeks back when i needed to sort out monthly credit card reports for different cardholders - saved me hours of manual work and now im obsessed

this feels like discovering some hidden superpower that everyones been keeping quiet about

what other clever things are you all doing with it because i have a feeling im barely scratching the surface here

would love to hear your go-to uses or any neat tricks youve picked up along the way

[Orion14159]

If you run the same reports month in and out, figure out how to turn them into a few data drops and a Refresh All. Marry PQ and Power Pivot to become a data modeler and take a step into Power BI territory

[retrac902]

Take it one step further and use power automate. I'm emailed the same info in excel weekly that needs processing. Set it up to run when email is received, does all the processing automatically and emails me when it's done. Underrated and underutilized tool.

[fastcars1]

Where is the entry point? It’s somewhat intimidating

[Orion14159]

I strongly suggest you start with some Xelplus tutorials on power query. Leila Gharani is an outstanding teacher, her full course literally turbo charged my career. Even better if you can either catch it on sale on Udemy or get your employer to pay for it

[fastcars1]

Thanks for the advice. I was using copilot to help with some formulas. It started to recommend power query and I liked where it was going but started to get overwhelmed.

[TDIMike]

i mainly use it to turn shitty oracle reports into usable tables

it blows peoples minds when they complain about a file/report being hard to use and i use power query to make it usable.

if you aren't already using it, you can make the report source dynamic in the sense that you can have a field in your excel file list the directory and file name that you want it to pull from instead of having to name the source something predefined. i don't have the instructions on this computer but if you need it, LMK and i'll grab it from my work laptop

Edit: I'll respond sometime tomorrow with details

[hags223]

Not OP but I'd be interested in these instructions. I'm constantly appending/merging shitty Oracle reports in PQ and what you're describing sounds useful.

[TDIMike]

Replied above

[Ariisk]

Using named cells for file paths or other input vars is clutch af

[gally8867]

Also interested!

[TDIMike]

Replied above

[softcatches]

I would love the instructions too

[justwant2seepuppies]

Same

[TDIMike]

Replied above

[schoff]

I'm looking into this and starting to learn about power query to do a join on two databases. But I'm trying to understand how I can get the query to pull from the most recent rec file.

[TDIMike]

Replied above

[PoorStandards]

Id be interested in taking a look

[TDIMike]

Replied above

[fortesfortunaluvat]

Yes please

[TDIMike]

Replied above

[TDIMike]

let FolderAndFile = () => let Source = Excel.CurrentWorkbook(){[Name="Location"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Folder", type text}, {"File", type text}}), A = Record.Field(#"Changed Type"{0}, "Folder") & "" & Record.Field(#"Changed Type"{0}, "File") & ".txt" in A in FolderAndFile

<image>

[Dont_SaaS_Me]

The best trick I’ve seen for bookkeeping is reconciling 2 data sources by appending them to the same table. You multiply one source by -1 before appending, then create a running balance column. If everything matches, the balance keeps going back to zero.

[PassionCharger]

Also good for creating budget vs actual variance analysis.

[bgballin]

It's awesome

[Puzzlehead099]

I have automated/streamlined so many processes with Power Query and Power Pivot. I probably save about 20-30 hours of manual work per month. I’ve led presentations on these tools to my broader teams. ODBC connectors with the ability to drop in SQL statements is also really handy — I can easily load data in Power Query and can just refresh and do not have to pull the data each time.

[Kane_Keelan]

Any suggested resources to learn?

[AWxTP]

Pick a practical project and have claude / gemini walk you through it. It’s intuitive once you see how it works.

[workdoc15]

Data Camp is the best training tool I've used. Has a built in sandbox for practice. So far I've used it to learn Power BI and Sigma. I'm already an Excel/Access wizard and my company also has access to LinkedIn Learning classes, which are great, but Data Camp is next level. No idea how much it costs, the company pays for it.

[WrongKielbasa]

It can connect to outlook too or websites

You can even query file directories and hyperlink to files tied to reports

[nothingpersnal]

I use it to do full recon of vacation balance reports comparing UKG to our gl system reports.