Power Query - Get distinct columns based on Rank Column

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