Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'd like to filter items from my dataset based on a list (l_Exceptions).
I was trying the solution in this post (which filters the dataset to any item found in the list) but instead would like to exclude the ones that are found in the list. The code below doesn't work unfortunately. Is there another way to approach this without having to resort to a merge and a custom column?
= Table.SelectRows(#"Filter InspectionType", each List.ContainsAny(l_Exceptions, {[InspectionNo]}, false))
Solved! Go to Solution.
I tried that but I'm getting an error (l_Exceptions is a list of text values, InspectionNo is a column of text values):
= Table.SelectRows(#"Expanded ExceptionList", each List.RemoveItems({[InspectionNo]}, l_Exceptions))
Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type
Edit: So I;m getting the error from the code aboe because List.RemoveItems returns a list and I need a logical value for the Table.SelectRows. Anyway, I found my answer here: Found the solution here: https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...
= Table.SelectRows(step, each List.Contains(l_Exceptions, [InspectionNo]) = false)
Hi @olimilo
You can use List.RemoveItems
https://docs.microsoft.com/en-us/powerquery-m/list-removeitems
I tried that but I'm getting an error (l_Exceptions is a list of text values, InspectionNo is a column of text values):
= Table.SelectRows(#"Expanded ExceptionList", each List.RemoveItems({[InspectionNo]}, l_Exceptions))
Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type
Edit: So I;m getting the error from the code aboe because List.RemoveItems returns a list and I need a logical value for the Table.SelectRows. Anyway, I found my answer here: Found the solution here: https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...
= Table.SelectRows(step, each List.Contains(l_Exceptions, [InspectionNo]) = false)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |