Power Query - unpivoting an Unpivot?
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 :smileysad:
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
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,
