Unpivot Rows to Column

[howeixiong]

Dear All

I would like to know if the following is possible?

I would have a large set of data and the number of columns depends on the no. of rows the data appears.

Some can be 1, 2, 3, 4 or 5 columns.

Thank you in advance!

Data
Unique Reference	Column 2
2404124231223097	TMPN
2404124231223097	3770808102FS
2404124231223080	9135793102FS
2404124231223085	REPAYMENT
2404124231223085	/BOOK
2404124231223085	3254675102FS
2404124231223084	USD10
2404124231223084	REPAYMENT
2404124231223084	039190
2404124231223084	1447267102FS
2404124231223094	BORROWING
2404124231223094	//BASE
2404124231223094	5971952102FS
2404124231223094	/ACC
2404124231223094	00005ILC240004

Result
Unique Reference	Column 1	Column 2	Column 3	Column 4	Column 5
2404124231223097	TMPN	3770808102FS
2404124231223080	9135793102FS
2404124231223085	REPAYMENT	/BOOK	3254675102FS
2404124231223084	USD10	REPAYMENT	039190	1447267102FS
2404124231223094	BORROWING	//BASE	5971952102FS	/ACC	00005ILC240004

[dufoq3]

Hi @howeixiong, different one here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc7BCoMwDMbxVxk9C03S1JijOjdk04o6xhDf/zVW78Ze/7/wddscMTASU0CiACqucOswjW4vTlsQgQoqBHosJ6aCbBRDFA2midnM3VT/hm5cb5bwTUovKwaKXEo0Jzibz3JHsOL1/iEgKKp5j8xCpVgf0MM0aZ7Ttx+fFvC+qZfOqlEFNdLlhK/b1mqQX+zfbU4A7Pb9Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Reference" = _t, #"Column 2" = _t]),
    GroupedRows = Table.Group(Source, {"Unique Reference"}, {{"All", each Table.FromRows(Table.ToColumns(Table.SelectColumns(_, {"Column 2"}))), type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", List.Transform({"1"..Text.From(Table.RowCount(GroupedRows))}, each "Column" & _))
in
    ExpandedAll

[AlienSx]

let
    Source = your_table,
    to_list = Table.ToList(
        Table.Group(Source, {"Unique Reference"}, {{"x", each [Column 2]}}),
        (x) => {x{0}} & x{1}
    ),
    cols = List.Max(List.Transform(to_list, List.Count)),
    to_table = Table.FromList(to_list, (x) => x, cols)
in
    to_table

[ronrsnfld · Accepted solution]

• Group by Reference
• Create a Record from each subgroup, generating the column names dynamically
• Create a unique, sorted list of all the column names
  • If there are more than 9, may have to do a custom sort

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Reference", type text}, {"Column 2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unique Reference"}, {
        {"Records", each Record.FromList([Column 2], List.Transform(List.Numbers(1, List.Count([Column 2])), each "Column " & Text.From(_))) }}),
    #"Col Names" = List.Sort(List.Distinct(List.Accumulate(
                        #"Grouped Rows"[Records],
                        {},
                        (s,c)=>s & Record.FieldNames(c)
                        ))),
    #"Expand Records" = Table.ExpandRecordColumn(#"Grouped Rows","Records", #"Col Names")

in
    #"Expand Records"

Result from your data above:

[wdx223_Daniel]

=Table.FromColumns(List.Zip(Table.Group(YourTableName,"Unique Reference",{"n",each {[Unique Reference]{0}}&[Column 2]})[n]))