Unpivot Duplicates into Single Row

[Anonymous]

Hi,

How can I get this:

Column 1	Column 2
A	5
A	8
B	3
B	4
C	3
C	4
D	6
D	1

Into this:

Column 1	Column 2	Column 3
A	5	8
B	3	4
C	3	4
D	6	1

Thank you

[ImkeF]

Hi @Anonymous  

will there always just be 2 columns or will the number of rows/columns change?

[Anonymous]

Hi @ImkeF 

There would be more columns in a scenario where there are more than 2 duplicates, which is fine. For example, if there are 3 G values, it's okay for there to be a fourth column that is mostly null except for row G.

[ImkeF · Accepted solution]

Hi @Anonymous  

please paste the code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJVitWBsCzALCcgyxjOMgGznOFiznAxFyDLDM4yVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column 1"}, {{"partition", each Table.AddIndexColumn(_, "Index", 1,1)}}),
    #"Expanded partition" = Table.ExpandTableColumn(#"Grouped Rows", "partition", {"Column 2", "Index"}, {"Column 2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded partition", {{"Index", type text}}, "en-GB")[Index]), "Index", "Column 2")
in
    #"Pivoted Column"

I'm creating a nested index to determine the column number like described here: https://www.youtube.com/watch?v=-3KFZaYImEY

[Anonymous]

Hello!! Un powerquery there is an option called pivote / unpivot rows, this may help!!! Have You tried to use this option???