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

[kekepania0529]

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)

[kekepania0529 · Accepted solution]

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]

Exactly what do you mean by "Last 3"

[kekepania0529]

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]

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.

[Rickmaurinus]

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]

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]

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]

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

[rubayatyasmin]

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.