Power Query - Avoid "Helper Queries" (+10 Cool Tricks)

What a way to avoid all those extra query steps when using files from a folder. NOT having to see all those steps Power Query adds is fantastic. You are a very skilled and intelligent guy. So keep digging into the Excel world to explain all those useful methods/tips. Thank you very much for sharing them.

Brilliant! Been using for a few years and always struggled with those helper files. Sometimes ending with 20 or even more. And it creates major issues if you just copy a query and maybe change the source only. It still works but I am always scared something goes wrong. Thanks so much. More of these please.

Great video! Thanks for sharing this very practical information. I have performed multi-file import operations similar to what was demonstrated in the lecture. However, I just retain the [Name] column from the initial metadata table and don't require the additional steps outlined in the presentation. I do remove the file extensions, of course. It propagates through the subsequent steps, as far as I can tell. I have been using and programming Microsoft Excel for 27 years but only started learning about Power Query some six months ago. It's a game changer! It's so cool that PQ can replace many of the VBA routines I have developed over the years to clean and transform data, all with just a few clicks. Throw in the added power of customizing the generated M Code script - I have created dozens of general-purpose PQ custom functions plus my own Excel VBA Add-in to help load multiple tables and ranges in one go - and my data cleansing capabilities have now reached the next level. I will never interact with Excel the same way again! Thank you kindly.

Simple, clear, focused --> understood and implemented in my work. Excelent presentation, clear use of words and examples are one of the best I have seen. Thank you for the tutorial.

Great stuff, I'll use that next time I touch a folder full of files. The only thing I'll do different is to filter out "grand total" and surplus header rows first. Filtering just based on error may (silently) remove and hide bad entries in the csv

Holy Cow!! This was an incredible mind blowing bit of knowledge! I for one have always HATED the helper queries because when you swap out entire sets of files in a folder, the helper queries are still looking for the filename that they first referenced and when that file is gone, they break. I have some pretty heavy queries that have too many steps. I know I can clean those up much better now. Thank you.

Just what I was looking for - getting rid of helper queries and hard coded steps. I love the fool proofing tips. Thank you.

Great video, and very clearly explained, as always. I also use that technique to avoid those confusing Helper Queries!

Ive been wanting to get around these helper queries for ages. Your guide is incredibly straight forward, and i cant wait to apply this new methodology in my future queries!

I hated these helper files from the beginning. So far I tried to avoid them by creating a function like let GetFiles=(Path,Name) => let Source = Excel.Workbook(File.Contents( Path & Name ), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = …, #xxx = … in #"xxx" in GetFiles and applied this function to the files in a folder. Your solution seems smarter as it even avoids having the function code. Brilliant. I will certainly give it try. Thanks for sharing your knowledge.

I like this format of going through long examples with a lot of embedded tricks. That Detect Data Types feature will save me a lot of time.

this is AWESOME! and, learned fundamentals in the process! thanks!!

Superb explanation and now I can get rid of my ugly, confusing helper queries. Always used to drive me mad they did and now I can control the data much more easily EDIT: I would add though when I do the Folder.Contents thing I like to have the steps expanded out in the Applied Steps window so that I can easily see the path/folders I've drilled down into. To do this, you add a line in the advanced editor to set a variable e.g. file = "expand" and put a comma on the end and then you will find that the steps following that are expanded out when you drill down into folders

This has been super helpful. I didn't know about the combined function. This is saving me a bunch of time. Thank you!

Excellent - and a great introduction for anyone looking to learn how to 'program' with power query.

This was excellent!. Thank you! Love your channel!

Honestly, this was very well explained, at a good pace, with lots of cool tips and tricks. Keep these vids coming, subscribed!

I sincerely thank you for this awesome, well-done, informative, mind-blowing tutorial.

Simply briliant. I have learned a trick or two, while considered myself as skilled in PQ

Wonderful! Excellent tips, except now it's increased my workload, as I'm going to have to go back and rework some queries to avoid the "helpers" and streamline multiple steps. Well done, well explained and illustrated.