Hi Chandeep. Excellent lesson! I like to try and solve before watching. Was able to generate the filtered results using List.Contains.. so.. must be learning something. Also, in watching your method, I realized that you can make the results return the opposite (all items not matching the filter) by changing =true to =false. Nice bonus lesson to understand how to take an action based on the results of the dynamic filter. Always good learning at Goodly! Thanks for sharing :)) Thumbs up!!
Hi chandeep...its a good way for small sets of data....on applying this formula on large datasets its time consuming....any idea if we can do it faster using list.buffer or table.buffer
Interesting. Thanks for sharing. One question though. One could combine the tables for filtering inner or outer join. Would that be different in performance?
@@GoodlyChandeep Glad I came across this video, I was doing this using joins and it was slowing down my query. Converting it into list and then using list contains is an elegant solution.
UPDATE: I applied this newly learned solution, but unfortunately with a large list up to 8000 entries the merge way seems to work faster. Did anybody encounter similar issues?
Hi Chandeep, your all videos are excellent, Thanks for creating such tutorials. I have one query, how to combine multiple queries into one using Table.combine function in power query dynamically. list should be picked from Excel Table.
Thank you for your video. Let say I want to keep such filter but instead of adding all A,B,C etc I want to enter in this list all and is such case I want see all the values.
Hi Chandeep, thank you very much for this excellent video, it's very helpful for me. However, I would like to add a specfic filter "ALL" in the list of filtered values in order to remove the filtered values and get my original list. Do you have an idea how to proceed please? Thank you very much for your help. Best Regards. Rajen
Hi Chandeep, That's a great explanation. It is possible to do the same filter when you want to filter records that start only with some chars?. #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each Text.StartsWith([Territory], "PT_PH_SM_")), I have try but only work if I have the complet value. //#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each List.Contains(Din_Filter_Territory,[Territory])=true), Kind regards, Marco
Hey I ran into an issue, The query works form me. However it takes very long to finish loading after I hit refresh. It does eventually load but after 5+ min. I do not have a lot of data. without this filter, the table loads in 15 secs.
Great video--- I just can't get my head around why we can't run a standard SQL query on a table, taking values from cells or ranges. The whole concept of Power Query appears to be broken from the foundation. I just want to Filter rows where values are matched.
hi, i have 2 columns Age and weight in Numbers., i try to filter max weight for the age below 10 yrs. i used following syntex. But it is saying that We cannot convert a value of type Function to type Logical. May i know how to solve this in powerquery or with index match. = Table.AddColumn(#"pstep", "Custom", if [Age]
You can try this. "greater" and "smaller" are values. They are the results from drilled down lists. let Quelle = Excel.CurrentWorkbook(){[Name="Tabelle22"]}[Content], #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type text}, {"Spalte3", Int64.Type}}), #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each [Spalte3] > greater and [Spalte3] < smaller) in #"Gefilterte Zeilen"
Is there a way to do this without an Exact match? Like in Excel if I were to filter and search one word everything containing that word would pop up. In Power Query, nothing would show up unless it was an exact match.
Hi chandeep, I want to filter reporting in employee master having two columns one is employee name and another one reporting person, have to list out under VP Vice president - ass manager - manager - Team leader - executive
Great Vide, never thought to use contains, i'd have just used an ListMax() or Listmin. I was trying something similar in PBi, and found this if I declared the min and max in the formula the keepfilter did not work ; CALCULATE([Measure], KEEPFILTERS(Table1[Item] = MAX(Table2[Item]) || Table1[Item] = MIN(Table2[Item]) ) ) Completely ignored the "AND" part (item = A ,B,C,D),, but if I made them a variable thus ; VAR lowerV = MIN(Table2[Item]) VAR upperV = MAX( Table2[Item]) RETURN CALCULATE([Measure],KEEPFILTERS(Table1[Item] = lowerV || Table1[Item] = upperV )) Then worked fine, just wondered if anyone reading this has a view as to why?
It's good but it's too slow for my data set. I created filter of 114 rows and my data set size is 200 mb. Am waiting from past 15 minutes. Seems like, need other trick
Hi, I will use name in slicer to filter content in power BI, I want to filter on basis that if I select ramu then output should be like given below. Siurce No Name FillterData 1111 Ramu 1111|1121 1112 Shaym 1112 1113 Dharam 1113 1114 Kuber 1114 1115 Roshan 1111|1115 1116 Ramu1 1112|1116 1117 Shaym2 1113|1117 1118 Dharam1 1114|1118 1119 Kuber1 1115|1119 1120 Roshan1 1116|1120 1121 Ramu1 1117|1121 Output No Name FillterData 1111 Ramu 1111|1121 1115 Roshan 1111|1115 Thanks in advance
Hi Chandeep. Excellent lesson! I like to try and solve before watching. Was able to generate the filtered results using List.Contains.. so.. must be learning something. Also, in watching your method, I realized that you can make the results return the opposite (all items not matching the filter) by changing =true to =false. Nice bonus lesson to understand how to take an action based on the results of the dynamic filter. Always good learning at Goodly! Thanks for sharing :)) Thumbs up!!
This video made my life much simpler. Another tutorial overly complicated this dynamic filter with multiselect parameters. thanks a bunch!
You explained exactly what I needed without adding unnecessary explanations. Thanks !
Very illuminating! Thank you!
Fantastic! Helped me complete a task I've had on my plate for a while. Thanks!
Jaadu bhai Jaadu .... Awesome & super helpful 👍
Hi chandeep...its a good way for small sets of data....on applying this formula on large datasets its time consuming....any idea if we can do it faster using list.buffer or table.buffer
Lovely straight forward explanation. Thanks
Plain and Simple. Its Awesome!
As always. SUPER TRICKS. Thank you very much
Interesting. Thanks for sharing. One question though. One could combine the tables for filtering inner or outer join. Would that be different in performance?
I suspect applying joins might slow down the query. But I haven't really tried it.
@@GoodlyChandeep thank you it is also that came in my mind. Would your solution also work with a filter list to exclude values?
@@dirkstaszak4838 Yes. Just replace the true with false
Read the comment from @wayne edmondson
@@GoodlyChandeep Glad I came across this video, I was doing this using joins and it was slowing down my query. Converting it into list and then using list contains is an elegant solution.
UPDATE: I applied this newly learned solution, but unfortunately with a large list up to 8000 entries the merge way seems to work faster. Did anybody encounter similar issues?
Super helpful and powerful. Just what I needed!
Glad it was helpful!
Great video and explanation. You have a new subscriber. Thanks!
Welcome aboard!
Perfectly what I needed. THANKS!!
Glad it was useful 💚
Great video. Solved my need.
Hi Chandeep, your all videos are excellent, Thanks for creating such tutorials. I have one query, how to combine multiple queries into one using Table.combine function in power query dynamically. list should be picked from Excel Table.
Awesome..useful..interesting..thank you
Thank you for your video. Let say I want to keep such filter but instead of adding all A,B,C etc I want to enter in this list all and is such case I want see all the values.
Hi Chandeep, thank you very much for this excellent video, it's very helpful for me. However, I would like to add a specfic filter "ALL" in the list of filtered values in order to remove the filtered values and get my original list. Do you have an idea how to proceed please? Thank you very much for your help. Best Regards. Rajen
Love this video, thank you!
Thanks bro. really this is is very help full me.
Glad it was helpful 😊
Very helpful, thanks!
Glad it was helpful!
Hi your explanation is awesome, but if i want to apply a dynamic filter in power query on or before date of end of current month... Pls help
Hi Chandeep,
That's a great explanation.
It is possible to do the same filter when you want to filter records that start only with some chars?.
#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each Text.StartsWith([Territory], "PT_PH_SM_")),
I have try but only work if I have the complet value.
//#"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each List.Contains(Din_Filter_Territory,[Territory])=true),
Kind regards,
Marco
Hey I ran into an issue,
The query works form me. However it takes very long to finish loading after I hit refresh. It does eventually load but after 5+ min.
I do not have a lot of data. without this filter, the table loads in 15 secs.
Thanks. It helped me in realtime.
Glad it helped PRAKAVIDS !
you are GREAT. THANKS
Can we implement the same over powerbi dataset through live connection..
This should sort my problem. Thanks
Great!
Hi Chandeep! Great video as usual.
What do you think about an inner join?
I think inner join is going to be faster.
Great video--- I just can't get my head around why we can't run a standard SQL query on a table, taking values from cells or ranges. The whole concept of Power Query appears to be broken from the foundation. I just want to Filter rows where values are matched.
Thanks it was very helpful
Thank you, this is really useful.
Nice video as usual, Would be really helpful..!
hi, i have 2 columns Age and weight in Numbers., i try to filter max weight for the age below 10 yrs. i used following syntex. But it is saying that We cannot convert a value of type Function to type Logical. May i know how to solve this in powerquery or with index match.
= Table.AddColumn(#"pstep", "Custom", if [Age]
Great video!
What would you do if your range os above 1000 different records?
Hi Chandeep very good video, just want to know if I have to filter value column with criteria greater than >50, less than
That condition can be added using the Filter Drop Down in Power Query
@@GoodlyChandeep thanks a lot
You can try this. "greater" and "smaller" are values. They are the results from drilled down lists.
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle22"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type text}, {"Spalte3", Int64.Type}}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each [Spalte3] > greater and [Spalte3] < smaller)
in
#"Gefilterte Zeilen"
brilliant stuff bro
How would you make a dynamic filter with values between Monday and Friday?
Is there a way to do this without an Exact match? Like in Excel if I were to filter and search one word everything containing that word would pop up. In Power Query, nothing would show up unless it was an exact match.
Hi chandeep, I want to filter reporting in employee master having two columns one is employee name and another one reporting person, have to list out under VP
Vice president - ass manager - manager - Team leader - executive
It’s a good one. Thanks!
Hi, how can I search for a text as part of another text/cell value?
if myCell = PartialText then myCell
else "Search not found in any cell"
I have a sheet with multiple blank rows in it. I want to load and transform the data of this sheet, but it load only top 30 rows. Please help me
Hi Sir. Great video! Is it possible to create a video similar to this but filter a date range like start date and end date as criteria or parameter.
Thank u very much!!!
Welcome Vanessa!
Thank you Sir👍
Awesome!
wonderful ................
Smooth, nice thx!
Great Vide, never thought to use contains, i'd have just used an ListMax() or Listmin.
I was trying something similar in PBi, and found this if I declared the min and max in the formula
the keepfilter did not work ;
CALCULATE([Measure], KEEPFILTERS(Table1[Item] = MAX(Table2[Item]) || Table1[Item] = MIN(Table2[Item]) ) )
Completely ignored the "AND" part (item = A ,B,C,D),, but if I made them a variable thus ;
VAR lowerV = MIN(Table2[Item])
VAR upperV = MAX( Table2[Item])
RETURN CALCULATE([Measure],KEEPFILTERS(Table1[Item] = lowerV || Table1[Item] = upperV ))
Then worked fine, just wondered if anyone reading this has a view as to why?
It's good but it's too slow for my data set. I created filter of 114 rows and my data set size is 200 mb. Am waiting from past 15 minutes. Seems like, need other trick
Everytime I do this it prompts a [Binary] cannot turn into Binary type… suggestions
Great
for some reason I don't get source but rather removerows in the formula
How can i filter data between 2 date ؟؟
Hi Sameh,I have your problem have you find a solution?
@@rezazadeh1422
M code
@@SamehRSameh i used "Contain.min" and "contain.max" and volla!!
Hi,
I will use name in slicer to filter content in power BI, I want to filter on basis that if I select ramu then output should be like given below.
Siurce
No Name FillterData
1111 Ramu 1111|1121
1112 Shaym 1112
1113 Dharam 1113
1114 Kuber 1114
1115 Roshan 1111|1115
1116 Ramu1 1112|1116
1117 Shaym2 1113|1117
1118 Dharam1 1114|1118
1119 Kuber1 1115|1119
1120 Roshan1 1116|1120
1121 Ramu1 1117|1121
Output
No Name FillterData
1111 Ramu 1111|1121
1115 Roshan 1111|1115
Thanks in advance