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" 

ronrsnfld's avatar
ronrsnfld
Super User
3 years ago

Exactly what do you mean by "Last 3"

kekepania0529's avatar
kekepania0529
Helper I
3 years ago

Last 3 of Ticket #

 

  • Match on Branch and Last 3 of Ticket #, keep rows where Ticket #s are different; remove rows where Ticket #s are the same.
  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    If I understand you correctly:

    • Group by Branch and Last3
    • Aggregate by returning unique list of TicketNo's only if there is more than one
    • Expand the group and filter out the nulls
    let
    Source = Excel.CurrentWorkbook(){[Name="Table30"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ShipBranch", Int64.Type}, {"Last3TicketNo", type text}, {"TicketNo", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ShipBranch", "Last3TicketNo"}, {
       
        {"More Than One", each
            if List.Count(List.Distinct([TicketNo])) = 1 then null else 
                List.Distinct([TicketNo]), type list}
        }),

    #"Expanded More Than One" = Table.ExpandListColumn(#"Grouped Rows", "More Than One"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded More Than One", each ([More Than One] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"More Than One", type text}})
in
    #"Changed Type1"

     

    If this is not what you want, please provide a sample of your expected results from the data you provided.