Forum Discussion
Advanced filtering in power query - multiple conditions
Dear community member,
I am struggling with filtering and using multiple conditions within a few columns. I think that Table.SelectRows should solv it somehow, but not sure. I want to keep rows in my dataset that match the following conditions.
Column CDN is ABC123 values, could be duplicated (more than one in the list)
Column P values can be any number starting from 1 up to 999;
Column T values can be only "A00" or "B00" or "1PL";
Columns TDV and TD are numeric values
The list itself may have from 1 up to ~10 000 lines/rows
Conditions:
1) if there is a certain row where Column CDN = "X" and Column T = "1PL" and Column P = "Y" , than keep this row;
OR
2) if there is a certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y" , than keep this row;
AND
3) if there is a certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y", than check all list from begining & delete/remove row where
Column CDN = "X" and Column T = "B00" and Column P = "Y" if so
OR
4) if there is NO certain row where Column CDN = "X" and Column T = "A00" and Column P = "Y", than check all list & keep row where
Column CDN = "X" and Column T = "B00" and Column P = "Y" if so
On the picture below you can see in red lines that should be removed/hide after filtering , e.g. there should be only non-red lines.
Thank you all in advance
Cheers!
Alex
Hi Anonymous ,
Please try this m code formula:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNNboMwEIXvwjoaza89XqYRiEWDmqZVQVHuf41YQNVawSgLkBfv87w3M77dGtb2isiOJIjn/nTU5tBQ/o6I+c+MDs75ZBG0uR92iLeVCAHY8gkBN4Cfc4cFQIyQwiv6xRIpA82OgDccrXr+u18jiNfuf75eFIJW7YytSaFXdYeFzMFtH1kcaTAFrEYe/GuwskWSDGK1p4N/ly2N4gL4rCfDmPWYeuECMHKQuWYqItQBiTCn2jbUdp+lI0t5aqEe+R+wtNWi8bJ45GkbuVyoqCGBIWK1RN+dysmRSgCfSQTyfeR3l/KMU6zVGK9TLGNrnkN1mcZh8vLtmK279Jo+SNqTT6OvL4E+3lfVqr0/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CDN = _t, P = _t, T = _t, TDV = _t, TD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CDN", type text}, {"P", Int64.Type}, {"T", type text}, {"TDV", type number}, {"TD", type number}}), GroupedTable = Table.Group(#"Changed Type", {"CDN", "P"}, {{"Count", each Table.RowCount(_), Int64.Type}}), MergedTable = Table.NestedJoin(#"Changed Type", {"CDN", "P"}, GroupedTable, {"CDN", "P"}, "GroupedData", JoinKind.LeftOuter), ExpandedTable = Table.ExpandTableColumn(MergedTable, "GroupedData", {"Count"}), FilterTable=Table.SelectRows(ExpandedTable,each ([T]="A00" or [T]="1PL") or ([T]="B00" and [Count]=1)), RemoveColumn=Table.RemoveColumns(FilterTable,"Count") in RemoveColumnBefore
After
Best Regards,
Bof
8 Replies
Anonymous What is X for CDN here? Current row's value?
variables such as "X" and "Y" are related to each current data list in cell.
For example,
CDN for both lines in the list = 24ES002801300MHCA4
P for both lines in the list = 1
T = A00 , B00
Thank you
use the following comination of conditions
FilteredRows = Table.SelectRows(Source, each // Condition 1: Keep rows where CDN = "X" and T = "1PL" and P = "Y" ([CDN] = "X" and [T] = "1PL" and [P] = "Y") // Condition 2: OR keep rows where CDN = "X" and T = "A00" and P = "Y" or ([CDN] = "X" and [T] = "A00" and [P] = "Y") // Condition 3: OR remove rows where CDN = "X" and T = "B00" and P = "Y" // only if a row with CDN = "X", T = "A00", and P = "Y" exists or (Row1Exists and not ([CDN] = "X" and [T] = "B00" and [P] = "Y")) // Condition 4: OR keep rows where CDN = "X" and T = "B00" and P = "Y" // if no row with CDN = "X", T = "A00", and P = "Y" exists or (not Row1Exists and [CDN] = "X" and [T] = "B00" and [P] = "Y") )Hi Omid_Motamedise ,
thank you for a code, however there is an error pop ups:
"Expression.Error: The name 'Row1Exists' wasn't recognized. Make sure it's spelled correctly."
Also, how can I replace values X and Y to reflect actual data in the cell for a particular row?
Thank you
Hi @Bof,
Basically, the rows which may be deleted are related to those were values B00 in column T.
To get the conditions easier:
1) all lines where "A00" either "1PL" in columns T - should remain
2) for lines where B00 in column T , it depends:
2.1. If there is an another row where columns CDN and P have identical values to our current line - than delete line with B00 and leave line with A00
2.2 if there is NO such row where CDN and P columns values are identical to our line - than keep this one
see examples:
B00 should be deleted , since there is already another row with A00 and the same values in first two columns;
Lines 1 and 4 B00 should remain (those are only lines for values in CDN and P together), however Line 3 B00 should be deleted (because there is already a line with A00 and identical values in first two columns)
3. All lines should remain because its either A00 or 1PL.
hope it helps
cheers!
Hi Anonymous ,
Please try this m code formula:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNNboMwEIXvwjoaza89XqYRiEWDmqZVQVHuf41YQNVawSgLkBfv87w3M77dGtb2isiOJIjn/nTU5tBQ/o6I+c+MDs75ZBG0uR92iLeVCAHY8gkBN4Cfc4cFQIyQwiv6xRIpA82OgDccrXr+u18jiNfuf75eFIJW7YytSaFXdYeFzMFtH1kcaTAFrEYe/GuwskWSDGK1p4N/ly2N4gL4rCfDmPWYeuECMHKQuWYqItQBiTCn2jbUdp+lI0t5aqEe+R+wtNWi8bJ45GkbuVyoqCGBIWK1RN+dysmRSgCfSQTyfeR3l/KMU6zVGK9TLGNrnkN1mcZh8vLtmK279Jo+SNqTT6OvL4E+3lfVqr0/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CDN = _t, P = _t, T = _t, TDV = _t, TD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CDN", type text}, {"P", Int64.Type}, {"T", type text}, {"TDV", type number}, {"TD", type number}}), GroupedTable = Table.Group(#"Changed Type", {"CDN", "P"}, {{"Count", each Table.RowCount(_), Int64.Type}}), MergedTable = Table.NestedJoin(#"Changed Type", {"CDN", "P"}, GroupedTable, {"CDN", "P"}, "GroupedData", JoinKind.LeftOuter), ExpandedTable = Table.ExpandTableColumn(MergedTable, "GroupedData", {"Count"}), FilterTable=Table.SelectRows(ExpandedTable,each ([T]="A00" or [T]="1PL") or ([T]="B00" and [Count]=1)), RemoveColumn=Table.RemoveColumns(FilterTable,"Count") in RemoveColumnBefore
After
Best Regards,
Bof
