Forum Discussion

kekepania0529's avatar
kekepania0529
Helper I
3 years ago
Solved

Power Query - Filter rows at change in Column A & Column B, where Column C has more than 1 value

I have a power query table that has a list of Branches and Ticket #s. I need to filter the list where there are multiple unique Ticket #s per Branch & Last 3 of Ticket Number combo.   In the screen...
Need Help
  • kekepania0529's avatar
    kekepania0529
    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" 

kekepania0529's avatar
kekepania0529
Helper I
3 years ago

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 #.

 

Rickmaurinus's avatar
Rickmaurinus
Helper V
3 years ago

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.