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

Learn how to avoid creating "Helper Queries" when extracting data from binary files.
Plus, see 10 incredibly useful Power Query & "M" code tricks to save valuable time and perform unthinkable data transformation acts.

"M" code examples (File download link:)

00:27 When & why Helper Queries are created
01:29 Video Objectives
02:11 Avoiding Helper Queries when Working with CSV files
04:18 Filtering by File Extension (i.e., file type)
05:03 Filtering by Path (i.e., file location)
05:44 Removing unnecessary Meta Data
05:54 Utilizing "Helper Queries"
07:03 Removing unwanted rows using "good" errors
07:36 Avoiding "Helper Queries" when working with text files
07:50 Extract Binary content using a function
09:06 Filling in the missing steps
10:26 Avoiding "Helper Queries" when working with Binary files
12:57 Filtering nested tables before data extraction
15:07 Sorting columns during column extraction
15:54 Renaming columns during column extraction
16:37 Removing total rows using "good" errors
17:33 Find / Replace values

[robbe]

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.

[ExcelWithChris]

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.

[mightydrew]

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

[Adam_K_W]

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.

[BrvKor]

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.

[serdip]

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.

[paser]

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

[tinhoyhu]

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.

[RichardJones]

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

[FranzL]

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.

[SaladSharkGaming]

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!

[DanKnight]

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.

[iankr]

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

[michaelt]

In your Expand_CSV step, couldn't you change the header names there? This would avoid the need to promote the first row to name the columns?

[reggydavis]

what are the gains in speed? I'm struggling to the major benefit here. Can't you just ignore the helper queries? It's so much easier to click the button than go through these steps.

[AlvaroFreireAF]

Great job!! However, in my case it was not so simple since my CSV files have a more complex encoding.
When I first tried to use the Csv.document command, it threw an error. To work around the problem, I first expanded using the normal process and copied the formula from the Transform Sample File located in the help folder.
After that, just replace the expression “Parameter1” with “Content”.
This was the original formula:
Csv.Document(Parameter1, [Delimiter=";", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None])
This is the new one:
Csv.Document([Content], [Delimiter=";", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None])

[alman]

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

[Mephistopheles]

A gentleman and a scholar. Thank you so much, dude. Now I get to spend the day optimizing/rebuilding all my tools.

Question for you: does this speed up the performance due to not having all that overhead?

[mostafahwafy]

Amazing For any one dealing with multiples monthly files types 🤩

[HVACALC]

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