Filter Nested Tables before Expanding in Power Query

If you're an avid Power Query user you'll often need to filter nested tables before you expand them in Power Query. In this video I'll share 2 techniques to apply a single or multiple value filter to tables before expanding them.

===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

===== LINKS 🔗 =====

===== CONTACT 🌐 =====

===== CHAPTERS =====
0:00 Intro
0:28 How Expanding and Filtering works in Power Query
4:40 Applying a single value filter before expanding
8:35 Applying multiple value filter before expanding
12:25 My Courses

===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers!

- - - - -
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[ExcelWithChris]

Another great one! Cannot wait for the M-code course. Greetings from South Africa.

[boissierepascal]

(From France) I found it very useful and well explained. Thank you. Your tutos are clear, clever. It's brilliant !

[bbiggie]

Thanks Goodly. I used this method and really struggled with refresh performance as it was filtering through hundreds of thousands of rows. This did give me the idea, however, to instead pull out only the first row from each column and filter on that instead rather than adding the entire filtered table as a column. I only needed the first row and it made an incredible difference in refresh speeds.

[YasirKhalid]

Awesome video! I had been wondering about this for a while but didn't know how to exclude before expanding. This was making my dynamic product table unnecessarily heavy as it was merging 3 tables on 3 different keys. Now I know what I need to alter to make the refreshes more efficient

[williamkiarie]

Thanks bro. This deserves a share!! Just 2 days ago I had to access a value in the first row/column position and use it to filter before expanding. This is helpful

[BrainyBrunetteBarbie]

Thank you for the EXCEL-lent (sorry for the pun) contextual explanations in your videos. I just found your channel and have already learned so much!

[pravinshingadia]

Recently started watching your videos and they are brilliant! Currently in hospital and intend to binge watch them all.

[paspuggie]

How weird, I did exactly the same filters on nested tables and List Contains recently to get a similar output. Your video proves I must be getting good. Still, I watched some of your previous videos to help me get there, thx Goodly👍

[jackylow]

Thanks and appreciate. Awesome knowledge and helpful for myinvois case.

[edsonschalm]

Hi Chandeep, I just found you a couple of weeks ago and just love your videos. You have a way of explaining that is great. I tried it in my project but with different workbooks and it returned empty tables. But I'll make changes to my date so they are all in the same workbook, in different sheets, and test it again. Thank you.

[suki]

Excellent Ninja-level secrets...Thanks very much, Chandeep!

[lynnetitley]

Hi! This is exactly the process I was looking for. Really enjoyed your presentation style & will be watching some of your other videos for tips! One question though - for the second part, where there are multiple tab names, I can get the query working if I use a 'direct input' List as a source table (i.e. type the tab names I want into a query in curly brackets). However, if, instead, I consume that list of names from an Excel table elsewhere in the workbook into a query, change the type to List, and then switch the main query over to reference the new queried List, the main query breaks (the dynamic "Table" values in my Custom column become hard-coded "[Table]" text. Any thoughts on why this might be?

[Travel-Costa-Rica]

This is a great tip. This also replaces the use of the Combine button, which I believe makes it easy to load the data. Greetings from Costa Rica

[Chris_in_fremantle]

Very clever, this technique should improve my refresh performance, thank you.👍

[EricaDyson]

Excellent. Just the sort of thing I need to do a lot. Mega thanks. A really great explanation!

[uahnbu]

Thank you so much. The very video I'm looking for, after hours of manual trial and error!!!

[allisontaladay]

Thanks! Was getting a stackover flow error in my dataflow and needed to filter the data before expanding.

[raimundojs]

Perfect! Once again, I can't thank you enough.

[vinhwizard]

Awesome, it's easy but useful. Thank you so much

[wayneedmondson]

Hi Chandeep. This is an awesome trick! Already used it to speed up some of my queries. Thanks for demonstrating. Thumbs up!!