Power Query - unpivoting an Unpivot?

[Bare]

Hi,

I'm new to the forum & appreciate assistance on a nagging transformation problem using Power Query.

I extracted & transformed a source table using PQ up to the point where the table looks as below:

Current state

Col A contains the main IDs & are in duplicate because of multiple values in Col B. Sometimes, as is the case with ID 45, I may multiples of more than 2. On the face of it, this looks like an unpivot table. However my target load table should look something like this:

Target state

I want Col A as unique values with only the first two corresponding attributes of Col B placed as 2 additional columns on the same row of that unique ID. This is 

I have tried different variations of unpivot, pivot & transpose in PQ. Sadly, no luck

I am not yet adept at M language but ready to give it a try (if this will help me).

Any help? Suggestion? Much appreciated. Thanks

[v-sihou-msft · Accepted solution]

@Bare

In this scenario, you need to add an index column to rank each row within Column1 group. Then filter the RANK <=2. Group rows within Column1 group again the and Transpose Column2.

Please refer to Power Query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjMwUNJRcnRy1jUyVorVQRYwNAILGAIldJScXV3APFNTkLSbr5OugSG6AES9iSmQHxLkDVOA4KPLAy2MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    GroupedRows = Table.Group(Source,"Column1",{"Data",each _,type table}),
    Rank = Table.TransformColumns(GroupedRows,{"Data",each Table.AddIndexColumn(Table.Sort(_,{"Column2",Order.Ascending}),"Rank",1,1)}),
    Expand = Table.ExpandTableColumn(Rank,"Data",{"Column2","Rank"},{"Column2","Rank"}),
    #"Filtered Rows" = Table.SelectRows(Expand, each [Rank] <= 2),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Rank"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Column1"}, {{"Data", each _, type table}}),
    Transpose = Table.TransformColumns(#"Grouped Rows",{"Data",each Table.Transpose(Table.RemoveColumns(_,{"Column1"}))}),
    #"Expanded Data" = Table.ExpandTableColumn(Transpose, "Data", {"Column1", "Column2"}, {"Data.Column1", "Data.Column2"})
in
    #"Expanded Data"

Regards,

[Bare]

Thanks @v-sihou-msft !!

It works ! 

Very much appreciate. I need to up my game in M language it seems.