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 screenshot below:

  • Branch (02) & Last 3 (AEL) has 1 ticket # (TELAEL)
  • Branch (02) & Last 3 (AUP) has 1 ticket # (TEHAUP)
  • Branch (02) & Last 3 (BDL) has 2 ticket #s (TAZBDL & TERBDL)

I need filter the table to remove the rows for 

  • Branch (02) & Last 3 (AEL) has 1 ticket # (TELAEL)
  • Branch (02) & Last 3 (AUP) has 1 ticket # (TEHAUP)

and keep the rows for 

  • Branch (02) & Last 3 (BDL) has 2 ticket #s (TAZBDL & TERBDL)

 

 

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" 

9 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, kekepania0529 

     

    From the drop down of the ticketNo column uncheck TELAEL and TEHAUP. 

    Your table will be filtered. 

     

    Thanks

     

    Did I help? If yes, hit 👍 and accept this answer as solution. 

  • kekepania0529's avatar
    kekepania0529
    Helper I
    3 years ago

    This is just a sample of the spreadsheet. The report needs to be dynamic and autofilter everytime it is refreshed.

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    3 years ago

    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.

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

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

         

         

  • kekepania0529's avatar
    kekepania0529
    Helper I
    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"