Forum Discussion
Power Query - Filter rows at change in Column A & Column B, where Column C has more than 1 value
- 3 years ago
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"
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"