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.
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
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
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?
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
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.
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👍
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?
This is very useful !! But what If I would like to filter the data from "Data-table" Header with specific Header like region contain "North".. Would it be possible to do after following these steps...
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.
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
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
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.
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.
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.
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.
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
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?
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.
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
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?
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
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
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 ?
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!
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 :(
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?
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?
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)
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?
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
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
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.
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?
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
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.
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
@@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.
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
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.
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..
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?
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.
Another great one! Cannot wait for the M-code course. Greetings from South Africa.
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
Glad it helped!
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
Glad it helped!
(From France) I found it very useful and well explained. Thank you. Your tutos are clear, clever. It's brilliant !
Glad it was helpful!
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?
You don't need to explicitly write = true. It can be omitted, but you can use = false to exclude the ones in MyList!
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
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.
Hey Edson, Glad you liked my work!
Cheers
Recently started watching your videos and they are brilliant! Currently in hospital and intend to binge watch them all.
Hope you are doing well!
Glad you found my work interesting.
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👍
Thanks Paul! Glad I could help
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!
Thank you for your inspiring words 😊
Thanks! Was getting a stackover flow error in my dataflow and needed to filter the data before expanding.
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
Glad it was helpful!
This is nothing but a great video!
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
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?
Excellent Ninja-level secrets...Thanks very much, Chandeep!
This is very useful !! But what If I would like to filter the data from "Data-table" Header with specific Header like region contain "North".. Would it be possible to do after following these steps...
A question. Could you have done Table.SelectRows([Custom], each ([Name] in MyList)) ?
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.
Awesome efficiency video! M language is indeed an overlooked power skill that can make a difference
Indeed Bare! Hope you're doing well
@@GoodlyChandeep Thanks Chandeep - yes doing well :-)
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
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
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.
Informative Video
What If nested table filtered before expand with nested list (not by single list with mulitple value)
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.
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.
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.
Very clever, this technique should improve my refresh performance, thank you.👍
Excellent. Just the sort of thing I need to do a lot. Mega thanks. A really great explanation!
Thank you so much. The very video I'm looking for, after hours of manual trial and error!!!
Im glad you found the solution.
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
Sure.. thanks for the suggestion
Yes, having practice file would be awesome
Still using this one. Thanx Goodly!
Awesome, it's easy but useful. Thank you so much
Great video! Can this be used with csv files?
Another life saver! Thank you!
You bet!
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?
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.
Is it possible to filter without adding a column using transform?
Perfect! Once again, I can't thank you enough.
Glad you liked it!
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
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?
Hi Chandeep. This is an awesome trick! Already used it to speed up some of my queries. Thanks for demonstrating. Thumbs up!!
Thanks Wayne! More to come :)
@@GoodlyChandeep Great! Looking forward to it!!
Very interesting. Could this same process be used to filter either a date range or a Date > xx/xx/xxxx?
You probably can!
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
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
Many thanks, Chandeep.
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
Excellent, Chandeep
Can we load every table in the row as a separate table in Power BI.
Do you need the “each” on the first select?
Hi thanks for the video. I think it would be useful to upload the the practice files here.
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 ?
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!
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 :(
Thank you. Your videos are very helpful for me.
Glad to hear that!
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?
how about filter by text contains? If I want to filter the https from dynamic columns of the table?
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?
Thanks! Really useful and ingenious tip!
Glad it was helpful!
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)
You can create a list for your headers, then you can use that list to select which header columns to keep.
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?
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
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
Hi Brother. i have a question... instead of filtering, how can i group the columns of that closed table before expanding it?
Great video!
Glad you enjoyed it
amazing explanation
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.
Awesome 👍. Would you make a video on connecting to a SharePoint folder?
thank you for the tips. really appreciate it!! 👍🏻
Glaf you like them!
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?
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
what if i need filter the nested table date filed between the outer table start date and end date.
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
Great video my friend!! super useful!!
Glad it was useful!
Amazing trick
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.
Amazing!!!!!
Well done !
Very nice trick
You rock buddy :)
Really good!
Thank you!
You are welcome!
Nice one! 👏
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??
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
Thanks, but i have a bit another case.
I need get all rows from nested tables in certain column as list with commas...
this should help - th-cam.com/video/jLpgt-wptH4/w-d-xo.html
Amazing.thanks
Very good 👍
"Expression.Error: We cannot apply field access to the type Text." came up when scripting to remove empty columns inside a nested table.
Awesome 😮
Thanks Willian!
Very cool!
Thanks!
woah that’s generous Bart! thanks so much!🙏
@@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.
very cool!
Thanks 💚
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
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.
@@GoodlyChandeep Product id is the primary key the products master table. Product id is the foreign key in the sales table wid duplicate values
@@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.
🥇Dankeschön
Welcome!
Wowww explanation but source of ur vedioes r not available for practice chandeep
Great 👍👍
But name of sheets doesn't appear ?
You can expand it from the column. I chose not to expand it.
When the video « transform » before expanding?
Power query m code lec needed
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..
Sorry for that
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
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?
Wonderful !!!