Forum Discussion

howeixiong's avatar
howeixiong
Regular Visitor
2 years ago
Solved

Unpivot Rows to Column

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 ReferenceColumn 2
2404124231223097TMPN
24041242312230973770808102FS
24041242312230809135793102FS
2404124231223085REPAYMENT 
2404124231223085/BOOK
24041242312230853254675102FS
2404124231223084USD10
2404124231223084REPAYMENT 
2404124231223084039190
24041242312230841447267102FS
2404124231223094BORROWING
2404124231223094//BASE
24041242312230945971952102FS
2404124231223094/ACC
240412423122309400005ILC240004

 

Result     
Unique ReferenceColumn 1Column 2Column 3Column 4Column 5
2404124231223097TMPN3770808102FS   
24041242312230809135793102FS    
2404124231223085REPAYMENT /BOOK3254675102FS  
2404124231223084USD10REPAYMENT 0391901447267102FS 
2404124231223094BORROWING//BASE5971952102FS/ACC00005ILC240004
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago
    • 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:

     

     

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago
    • 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:

     

     

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago
    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
  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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