Power Query - Get distinct columns based on Rank Column

[Anonymous]

Hi All

I am stuck in a scenario where i have assigned ranking to different Types.
Data contains repeated types values and i would like to get only 3 of these values in the result.
If 3 does not exist, top 2 type values to be my output. If same type exists pick any one type even if other values are different.

Consider the below scenario:

                  ID Type                ID Num      ID Issuer Ranking

(Sceanrio 1) Driver's License 13456         ABC        1

                    Driver's License 8970  DEF 1

                    Passport 456 XYZ 2

(Scenario 2 )Driving License 111 OPQ 1

                    Driver's License 232 gfjh 1

                    Driver's License 234555 gjghjs 1

                    Passport 222 aaaa 2

                    Other 333 aseg 3

                    Passport 12322 gbh 2

Desired output

                             ID Type ID Num ID Issuer Ranking

Scenario 1 result -Driver's License;Passport 13456;456 ;XYZ 1;2

Scenario 2result -Driving License;Passport;Other 111;222;333 OPQ;aaaa;aseg 1;2;3

Thanks in advance

[BA_Pete · Accepted solution]

Hi @Anonymous ,

The following code should work for both scenarios. It uses two Group Bys to bundle up the [ID Type]s first, then to create the value lists:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8slMTs1LTlXSUTI0MjYxBdKOTs4gnlKsDrKsmbmFpQGQdnF1g8sGJBYXF+QXlQAFTEzNgGREZBSQNAJL+pdkpBYBecbGxkAysTg1HcRRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Type = _t, ID_Num = _t, ID_Issuer = _t, Ranking = _t]),
    
    groupIdType = Table.Group(
        Source,
        {"ID_Type"},
        {
            {"ID_Num", each List.First([ID_Num]), type nullable text},
            {"ID_Issuer", each List.First([ID_Issuer]), type nullable text},
            {"Ranking", each List.First([Ranking]), type nullable text}
        }
    ),
    addGroupColumn = Table.AddColumn(groupIdType, "GroupColumn", each "Group Me"),
    groupGroupColumn = Table.Group(
        addGroupColumn,
        {"GroupColumn"},
        {
            {"ID_Type", each Text.Combine(List.Distinct([ID_Type]), ";"), type nullable text},
            {"ID_Num", each Text.Combine(List.Distinct([ID_Num]), ";"), type nullable text},
            {"ID_Issuer", each Text.Combine(List.Distinct([ID_Issuer]), ";"), type nullable text},
            {"Ranking", each Text.Combine(List.Distinct([Ranking]), ";"), type nullable text}
        }
    )
in
    groupGroupColumn

It changes this:

...to this:

Pete

[Anonymous]

Thanks. that was quick.