Forum Discussion
Power Query - Filter rows at change in Column A & Column B, where Column C has more than 1 value
i ended up creating a list of unique Branch + Last3TicketNo + Ticket No then grouping on the Branch + Last3TicketNo combo and filtering out where there was only 1 ticket number per combo. Then I merged the results with the original query.
let
Source = #"Criteria 1",
#"Removed Other Columns" = Table.SelectColumns(Source,{"ShipBranch", "Last3TicketNo", "TicketNo"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"ShipBranch", "Last3TicketNo", "TicketNo"}),
#"Grouped ShipBranch & Last3TicketNo" = Table.Group(#"Removed Duplicates", {"ShipBranch", "Last3TicketNo"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All Rows", each _, type table [ShipBranch=nullable text, Last3TicketNo=text, TicketNo=nullable text]}}),
#"Filtered Count <> 1" = Table.SelectRows(#"Grouped ShipBranch & Last3TicketNo", each [Count] <> 1),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Filtered Count <> 1", "All Rows", {"TicketNo"}, {"TicketNo"}),
#"Removed Count" = Table.SelectColumns(#"Expanded All Rows",{"ShipBranch", "Last3TicketNo", "TicketNo"})
in
#"Removed Count"
Hey keke,
You could try the following:
1. First return all unique combinations of TicketNo, and Last3TicketNo.
2. Then Group these combinations by the Last3TicketNo column and count the occurrences.
3. Filter only the combinations that appear once.
4. Perform an inner join on the source dataset to only keep those rows.
You can do that by using:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBS0lEKcfVxdPUBMkBkrA5Zoh6OoQEgUSBJnqhjlJMLyFwQiaQ2iKqisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ShipBranch = _t, TicketNo = _t, Last3TicketNo = _t]),
#"Unique combinations" = Table.Distinct( Source[[TicketNo], [Last3TicketNo]] ),
#"Count of Characters" = Table.Group(#"Unique combinations", {"Last3TicketNo"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
ValuesToKeep = Table.SelectRows(#"Count of Characters", each [Count] = 1),
SourceTable = Source,
KeepRelevantRows = Table.NestedJoin(SourceTable, {"Last3TicketNo"}, ValuesToKeep, {"Last3TicketNo"}, "Custom1", JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(KeepRelevantRows,{"Custom1"})
in
#"Removed Columns"
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
It did the opposite. it kept the rows that have 1 unique ticket number per Branch + Last 3 of Ticket # combo.
I need to filter those row out and keep the rows that have more than 1 unique Ticket #.
I'm glad to hear that's working. From your description I understood you wanted to filter them out, but in the reverse case you just swap the '= 1' to '>1'.
If these suggestions helped you I'd appreciate a thumbs up for the effort. Thanks!
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.