Filter Nested Tables before Expanding in Power Query

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
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 -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Data Loading Tricks - • Data Loading Tricks in...
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨‍🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - [e-mail]
===== 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
- Source: goo.gl/fh3rEJ​
Official After The Fall KZbin Channel Below
kzbin.info/door/GQE...
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)
Full license here: creativecommons.org/licenses

[@bbiggie97]

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.

[@ExcelWithChris]

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

[@YasirKhalid1]

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

[@GoodlyChandeep]

Glad it helped!

[@vinhwizard]

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

[@EricaDyson]

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

[@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

[@GoodlyChandeep]

Glad it helped!

[@mergeop]

Another life saver! Thank you!

[@GoodlyChandeep]

You bet!

[@Chris_in_fremantle]

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

[@jeromeastier462]

Still using this one. Thanx Goodly!

[@boissierepascal5755]

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

[@GoodlyChandeep]

Glad it was 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!

[@GoodlyChandeep]

Thank you for your inspiring words 😊

[@edsonschalm3085]

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.

[@GoodlyChandeep]

Hey Edson, Glad you liked my work! Cheers

[@uahnbu]

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

[@GoodlyChandeep]

Im glad you found the solution.

[@suki9860]

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

[@antoniogarrido3058]

Thanks! Really useful and ingenious tip!

[@GoodlyChandeep]

Glad it was helpful!

[@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

[@GoodlyChandeep]

Glad it was helpful!

[@raimundojs9547]

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

[@GoodlyChandeep]

Glad you liked it!

[@pravinshingadia7337]

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

[@GoodlyChandeep]

Hope you are doing well! Glad you found my work interesting.

[@allisontaladay2985]

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

[@rubenmunozverdu7528]

You don't need to explicitly write = true. It can be omitted, but you can use = false to exclude the ones in MyList!

[@ziomekzedzielni1]

If u don't write TRUE even values that are not on the list will be included. It will be treated as if one record is on that list it's "ok" so all others will get an "ok" too. Writing TRUE is very important

[@seshakv]

Thank you. Your videos are very helpful for me.

[@GoodlyChandeep]

Glad to hear that!

[@inspiration1014]

Wonderful !!!

[@imover18]

thank you for the tips. really appreciate it!! 👍🏻

[@GoodlyChandeep]

Glaf you like them!

[@cesarsaldana3429]

Amazing!!!!!

[@michaelangellotti5741]

Well done !

[@kennethstephani692]

Great video!

[@GoodlyChandeep]

Glad you enjoyed it

[@agustinpena948]

Great video my friend!! super useful!!

[@GoodlyChandeep]

Glad it was useful!

[@cg24356]

Really good!

[@udusegbe]

Awesome efficiency video! M language is indeed an overlooked power skill that can make a difference

[@GoodlyChandeep]

Indeed Bare! Hope you're doing well

[@udusegbe]

@@GoodlyChandeep Thanks Chandeep - yes doing well :-)

[@iankr]

Many thanks, Chandeep.

[@bhakthasingh8198]

Excellent, Chandeep

[@smartwork4768]

Amazing.thanks

[@mnowako]

Nice one! 👏

[@entertainmentgalaxy971]

amazing explanation

[@paspuggie48]

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👍

[@GoodlyChandeep]

Thanks Paul! Glad I could help

[@wayneedmondson1065]

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

[@GoodlyChandeep]

Thanks Wayne! More to come :)

[@wayneedmondson1065]

@@GoodlyChandeep Great! Looking forward to it!!

[@Bhavik_Khatri]

Very nice trick

[@DrivingLessonsnet]

Very cool!

[@adamshrinah2075]

Thank you!

[@GoodlyChandeep]

You are welcome!

[@lynnetitley7976]

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?

[@shamf6624]

Amazing trick

[@martyc5674]

Very good 👍

[@GoodlyChandeep]

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@josealvesferreira1683]

very cool!

[@GoodlyChandeep]

Thanks 💚

[@YogeshSharma-ui7xx]

You rock buddy :)

[@willianthalles]

Awesome 😮

[@GoodlyChandeep]

Thanks Willian!

[@ilkercalsr8130]

Thanks a lot. Your approach to the issues always clever. I think this approach will be very useful when to gather data from a database. Specially when you use multiple databases. Is it possible for you to make a video about retreiving data from dbs?

[@Osmanfayyaz]

Informative Video What If nested table filtered before expand with nested list (not by single list with mulitple value)

[@barttrudeau9237]

Thanks!

[@GoodlyChandeep]

woah that’s generous Bart! thanks so much!🙏

[@barttrudeau9237]

@@GoodlyChandeep It's accumulated my friend! I have listened to a lot of your videos and learned so much. You are a very good teacher! I'm looking forward to when you finish your M language course. I hope you keep up your great work, and thank you.

[@sharmarudra]

Awesome 👍. Would you make a video on connecting to a SharePoint folder?

[@Hortster]

Interesting video, thanks for sharing. I wonder how this method compares when used on large data sets (millions of rows) compared to expanding the table first and using List.Buffer when selecting rows from a list. Is it only the preview where performance is improved in the PQ Editor or also the load time to the workbook? I may have to experiment with it.

[@MrDonkey912]

All of the `*.Buffer` functions have the possibility to improve performance, but if it runs outside the allocated memory, you get a massive performance penalty. It depends on your query and environment, so you'll have to test it.

[@mihaelstancirevic4178]

Hi, great video! What if I do not want only filter, but also transform data at each filtering iteration? I started to use Power Query only recently and at work receive financial reports and traffic statistic files that for each month of each years, where the categories in each line of a column need to become column names and the name of a column that is "month-year" need to become rows at the beginning of the table. When I upload files from a folder, and suppose the format of tables in all files is consistent, I get a bunch of records. I want to filter each table, transpose it or do whatever needed with filtered rows, and finally merge everything in the same query. How to do that if I can't for instance filter everything to have what I need and then apply formatting? I need a function to do transformations at each filtering. I have tried with List.Generate and a custom function that captures formatting, but haven't succeeded so far.

[@AlvaroFreireAF1]

Great video! Can this be used with csv files?

[@marcbrain]

what about when the list has more tables instead of the sheet names? i’m working with a folder of pdfs that have multiple tables in each pdf. i would like to filter for just the tables that contain the word “UNBUNDLING” in them for further aggregation. any help would be greatly appreciated

[@StefanoVerugi]

please try the following (manually or with the advanced editor) : 1 - add a custom column with addcol=Table.AddColumn(Source, "Search", each Table.RowCount(Table.FindText([Data] ,"unbundling"))) 2 - filter the output using Table.SelectRows(addcol, each ([Search]>0)) Hope this helps

[@MohsenM-si3et]

Hi thanks for the video. I think it would be useful to upload the the practice files here.

[@pabeader1941]

A question. Could you have done Table.SelectRows([Custom], each ([Name] in MyList)) ?

[@GoodlyChandeep]

I guess no because each [Name] is a single value and Mylist will have multiple values. That would return a multiple true /false outputs. Table.SelectRows should just have one single true/false output for each value in the [Name] Column.

Thanks, very good video, I have this issue: I have different pdf files, these have different pages and tables, it may be possible to get the names of the queries so my query does not fail, since the files differ in pages and tables and my query fails

[@gerardboyle4812]

Thanks for all of your videos! Prior to expanding Tables in a column, is there a way to filter the columns by column header by exact match or contains a text string (say "PQ")? Please keep in mind that tables may be of different dimensions and column names will not be the same. (i.e. Table one has 25 columns in total where only 15 should be returned, Table two has 20 columns where only 10 should be returned, continue on to n # of tables)

[@kylevermillion4472]

You can create a list for your headers, then you can use that list to select which header columns to keep.

[@mshparber]

Great, but how can I pass the filter value from the outer table? For example if I have "Toys" in a Product Column of the outer table and I need the nested table only to have rows with "Toys", not as a hard-coded filter but it should get the filter value from the outer table. Please help

[@luisganhao1316]

Excelent video, How can aplly this tecnic whit an ODBC Conection? i have a table with about 160 000 rows, which correspond to 15 years of records and i want to filter from a specific year or between dates.

[@elcin35]

Thank you, im my example Power BI sees the empty rows , is there a way to filter this in advance? I mean, I dont want empty rows and filter them when I am filtering my list.

[@user-jk2uj4el5l]

Hi! I have an question about the file format. I am getting data from Oracle database, that is massive data and I am working with Power BI, so my source file is (Pbix). Is power bi format supported for this kind of filtering before expanding?! I should mention that I should filter before expanding, because I have lots of nested tables. You are using Excel file, so are using Excel wookbook formula, but my file is Power BI. Please, guide me through this process.

[@cg24356]

Great stuff! If I get it right, you are filtering 1 one out of 4 sheets in the excel file, correct? Do you know if it is possible to filter rows of 1 table if the xlsx file only contains 1 sheet? Example: Only expand the part of a table where a date is in the current year? Thanks!

[@GoodlyChandeep]

That can be done by multi-layered nesting to check the table for the condition and expand if the condition is met. The more complex the code the harder it is to maintain if something breaks :(

[@KlausTrapp]

🥇Dankeschön

[@GoodlyChandeep]

Welcome!

[@madhukommi6735]

This is great,I need to present data in table visual with sales line count where unit price is zero, By default unit price is don't summarize ,i sum the unit price fileds in the table visual field property and it is showing expected result..When I change this table visual to card ,it is ignoring unit price filter. Due to this value is changing...can you please help out on this?

[@user-po6ri3jw5e]

Great video and very well explained. But how can I use the content of a column of the loaded table as a filter? If I specify the name of the column directly in square brackets, I get the error message that this field does not exist, because in this case Power Query only recognizes the columns of the table that has not yet been expanded. If I write StepName[ColumnName], then no error message comes up, but nothing is filtered. Specifically, I have a table in which there are entries with date from and date to. A second table contains events with date. After the table join, I would now like to know only whether and if so, how many events there are in the respective period.

[@syedibrahimmusa6327]

Hi Chandeep, Is it possible to name the column before expanding ? I have choose the folder in power query which has many files. Before expanding I want to name the column, so that expansion will bring the columns together

[@madhukommi6735]

Thisi is great,I need to present data in table visual with sales line count where unit price is zero, By default unit price is don't summarize ,i sum the unit price fileds in the table visual field property and it is showing expected result..When I change this table visual to card ,it is ignoring unit price filter. Due to this value is changing...can you please help out on this?

[@alexzimmermachado3898]

I'm using table.selectrows but it seems to be a lot longer than just expand all the table and then do some filtering. Is there a cause for this? maybe because the this functions do more processing?

[@kasinasintuaus5172]

how about filter by text contains? If I want to filter the https from dynamic columns of the table?

[@donaldmillwood6308]

Can you use this trick for PDF files? - I have the same table as shown at timestamp 2:32 of this video except for the binary in the [Content] column are pdf files - I have tried "each Pdf.Tables([Content], [Implementation="1.3"], true)) and each Pdf.Tables([Content], true)) - both return errors?

[@mahamohan1]

I am learning a lot from your videos. I have a small request. Kindly make the file you are working available for downloading so that a beginner like me can practice side by side. Thanks in advance

[@GoodlyChandeep]

Sure.. thanks for the suggestion

[@Bhavik_Khatri]

Yes, having practice file would be awesome

[@s1ngularityxd64]

my query from folder(csv) takes ages to load since I changed it from hard coded to a drilled down reference list of columnnames. 😢 Maybe this could help me to reduce the payload of my initial data, if I only pick the columns I really need?

[@ishanmadan1745]

Hi sir! How can we filter using one particular column in a table?i.e. I want to filter out rows after applying filter only on a particular column of table

[@bagnon]

Is it possible to filter without adding a column using transform?

[@robrayborn1349]

Very interesting. Could this same process be used to filter either a date range or a Date > xx/xx/xxxx?

[@GoodlyChandeep]

You probably can!

[@sabarashid1513]

Can you please confirm if it works on a 4 million row of data. I tried it and it is kind of slow. Any advice would be appreciated. Thanks

[@narasimharabba7099]

I have one doubt about a table have some data middle of the header have their How to promote header without deleting rows in table data...please help with this task

[@surajdadge560]

Can we load every table in the row as a separate table in Power BI.

[@user-eu3bw2os5h]

I tried to merge master table that had a date with another table that had start and end dates, however, when I created the filtered column I filterd date >= startDate and date

[@frachiar4444]

At step at minute 11.40 I see that it works also without that "=true", because List.Contains already give me true/false. Right? Or I missed some good reason?

[@baza3a]

Hi Brother. i have a question... instead of filtering, how can i group the columns of that closed table before expanding it?

[@sadinenim5360]

what if i need filter the nested table date filed between the outer table start date and end date.

[@lionels839]

Hello, but I what a slowdown when using the LIST after filtering data with 500 000+ rows .From 2 min to 3 hours in my case

[@ivancuadros8731]

Do you need the “each” on the first select?

[@Rice0987]

Thanks, but i have a bit another case. I need get all rows from nested tables in certain column as list with commas...

[@GoodlyChandeep]

this should help - kzbin.info/www/bejne/oH3TmKdjrNWnfpY

[@harjitsingh30]

After filtering the required tabs from multiple worksheets and expanding the query, it does still take a lot of time to load on to a pivot. Am i missing something?

[@obedespinoza4185]

What if I need to filter the nested table based on a value extracted from the current table's row? How do I do this??

[@GoodlyChandeep]

In a custom column you'll have to capture the current table's row value and then apply the filer. Something like this let currentrow = [ColumnName] filteredtable = Table.SelectRows([ColumnwithNestedtables], each [Colname] = currentrow in filteredtable

[@namangarg7875]

I am trying to apply rankx fns in my sales table considering productid. Suppose my productid in the sales table has numeric values starting from 1 to 1000 and also has duplicate values in the sales table and i want to rank the product based on the product id... How to do that?? I have applied Rankx(all(sales table[product id]), [total revenue],, Desc, dense) My answer is coming wrong. Please help

[@GoodlyChandeep]

See the visual - where is the product ID coming from, the sales table or products master table? According to your measure it should be from the sales table. I can't say much unless I see your model / measure.

[@namangarg7875]

@@GoodlyChandeep Product id is the primary key the products master table. Product id is the foreign key in the sales table wid duplicate values

[@namangarg7875]

@@GoodlyChandeep if i had a chance to show you the visual over here i would definitely done that but here i m not allowed to share the visual.

[@AKHits-bn4jb]

Wowww explanation but source of ur vedioes r not available for practice chandeep

[@SamehRSameh]

Great 👍👍 But name of sheets doesn't appear ?

[@GoodlyChandeep]

You can expand it from the column. I chose not to expand it.

[@sujit3375]

Power query m code lec needed

[@lionels839]

When the video « transform » before expanding?

[@Lyriks_]

I really don't like these videos Chandeep ! They are way too short for the amount of concentrated knowledge you bring to excel community of powerusers..

[@GoodlyChandeep]

Sorry for that

[@GoodlyChandeep]

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@venkatraghavanr5169]

Here the filter criteria is hard coded which is "toys". In another scenario, when I tried to make the filters in nested table dynamic by giving reference to another column in the table (that is, filter should be dynamic based on each row in the table), it gives me an error "The field 'XXX' of the record wasn't found. Can you help ?