Forum Discussion

KDS's avatar
KDS
Helper I
4 years ago
Solved

Unstacking Data

I'm importing a table that contains some summary data.  After removing  A LOT of uncessary data, I'm left with the following:

 

The first column refers to the location.   The table below isn't displaying correctly, but there is no value above Field 9.

 

1Field 1Field 2Field 3Field 4 
1$500$550$200$300 
1Field 5Field 6Field 7Field 8Field 9
1$234$3234$223$982$399
2Field 1Field 2Field 3Field 4 
2$878$237$872$235 
2Field 5Field 6Field 7Field 8Field 9
2$552$432$321$132$532

... and so on.

 

I need to transform the data so that it's in the following format:

LocationField 1Field 2Field 3Field 4Field 5Field 6Field 7Field 8Field 9
1$500$550$200$300$234$3234$223$982$399
2$878$237$872$235$552$432$321$132$532

 

 

How do I go about accomplishing this?

 

Thanks!

 

 

  • Hi!  This will get you there.

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDoAgEEN/xRBHBy2cwA/4E4RNBxP/f1av5oijTn0lvaNQipvc4JZ9O9auEYy8Ubioc3XgRC/jqCIqoPMqLcVJMZqNolEyym07fNB9jwJ3jz4n6GlmEp+b63iKiVdEOtDJK/WnOfghKsGzKvQ5E51cUusJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
        ChangeType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
        SplitTables = Table.Split(ChangeType,2),
        TransformListTables = List.Transform ( 
            SplitTables,
            each 
                let 
                    formattedTable = Table.PromoteHeaders(_, [PromoteAllScalars=true]),
                    renamedCol = Table.RenameColumns(formattedTable,{{List.First ( Table.ColumnNames ( formattedTable ) ), "ID"}}),
                    selectCol = Table.SelectColumns(renamedCol, List.Select(Table.ColumnNames(renamedCol), each _ <> " ")),
                    unpivot = Table.UnpivotOtherColumns(selectCol, {"ID"}, "Attribute", "Value")
        
                in 
                    unpivot
        ),
        ListToTable = Table.FromList(TransformListTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandTableColumn(ListToTable, "Column1", {"ID", "Attribute", "Value"} ),
        Pivot = Table.Pivot(Expand, List.Distinct(Expand[Attribute]), "Attribute", "Value")
    in
        Pivot

     

6 Replies

  • Hi!  This will get you there.

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDoAgEEN/xRBHBy2cwA/4E4RNBxP/f1av5oijTn0lvaNQipvc4JZ9O9auEYy8Ubioc3XgRC/jqCIqoPMqLcVJMZqNolEyym07fNB9jwJ3jz4n6GlmEp+b63iKiVdEOtDJK/WnOfghKsGzKvQ5E51cUusJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
        ChangeType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
        SplitTables = Table.Split(ChangeType,2),
        TransformListTables = List.Transform ( 
            SplitTables,
            each 
                let 
                    formattedTable = Table.PromoteHeaders(_, [PromoteAllScalars=true]),
                    renamedCol = Table.RenameColumns(formattedTable,{{List.First ( Table.ColumnNames ( formattedTable ) ), "ID"}}),
                    selectCol = Table.SelectColumns(renamedCol, List.Select(Table.ColumnNames(renamedCol), each _ <> " ")),
                    unpivot = Table.UnpivotOtherColumns(selectCol, {"ID"}, "Attribute", "Value")
        
                in 
                    unpivot
        ),
        ListToTable = Table.FromList(TransformListTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandTableColumn(ListToTable, "Column1", {"ID", "Attribute", "Value"} ),
        Pivot = Table.Pivot(Expand, List.Distinct(Expand[Attribute]), "Attribute", "Value")
    in
        Pivot

     

    • KDS's avatar
      KDS
      Helper I

      jennratten with some tweaking, I was able to get your solution to work.  Thank you! I think I now what the above steps are doing, but would you mind adding comments so I can understand it better?  Thanks!

      • jennratten's avatar
        jennratten
        Super User

        Sure thing - here you are.  Please let me know if you need anything further.

        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDoAgEEN/xRBHBy2cwA/4E4RNBxP/f1av5oijTn0lvaNQipvc4JZ9O9auEYy8Ubioc3XgRC/jqCIqoPMqLcVJMZqNolEyym07fNB9jwJ3jz4n6GlmEp+b63iKiVdEOtDJK/WnOfghKsGzKvQ5E51cUusJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
            ChangeType = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
            // Split the current table into a list of tables, each of which is comprised of two rows.
            SplitTables = Table.Split(ChangeType,2),
            // Transform the list of tables.
            TransformListTables = List.Transform ( 
                SplitTables,
                // Apply the following transformations to each element (table) in the list.
                each 
                    let 
                        // Use the first row of data as the column headers.
                        formattedTable = Table.PromoteHeaders(_, [PromoteAllScalars=true]),
                        // Change the name of the first column of the table to ID.
                        renamedCol = Table.RenameColumns(
                            formattedTable,
                            {
                                {
                                    List.First ( Table.ColumnNames ( formattedTable ) ), 
                                    "ID"
                                }
                            }
                        ),
                        // Select columns whose names are not equal to a space.
                        // AKA Remove the column named " ".
                        selectCol = Table.SelectColumns(renamedCol, List.Select(Table.ColumnNames(renamedCol), each _ <> " ")),
                        // Except for the ID column, change all other columns to rows
                        // with the column names appearing in the new "Attribute" column
                        // and the values appearing in the new "Value" column. 
                        unpivot = Table.UnpivotOtherColumns(selectCol, {"ID"}, "Attribute", "Value")
            
                    in 
                        unpivot
            ),
            // Convert the list of tables to a table of tables.
            ListToTable = Table.FromList(TransformListTables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            //Expand the specified columns from all nested tables that appear in the column named Column1.
            Expand = Table.ExpandTableColumn(ListToTable, "Column1", {"ID", "Attribute", "Value"} ),
            // Pivot the original column names and values from rows back to columns.
            Pivot = Table.Pivot(Expand, List.Distinct(Expand[Attribute]), "Attribute", "Value")
        in
            Pivot
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDoAgEEN/xRBHBy2cwA/4E4RNBxP/f1av5oijTn0lvaNQipvc4JZ9O9auEYy8Ubioc3XgRC/jqCIqoPMqLcVJMZqNolEyym07fNB9jwJ3jz4n6GlmEp+b63iKiVdEOtDJK/WnOfghKsGzKvQ5E51cUusJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
        #"Rows to Records" = Table.Group(Source, "Column1", {"ar", each Record.Combine(List.Transform(List.Split(List.Transform(Table.ToRows(Table.RemoveColumns(_, "Column1")), each List.Select(_, each Text.Trim(_)<>"")), 2), each Record.FromList(_{1}, _{0})))}, 0, (x,y) => Number.From(x<>y)),
        #"Expanded Records" = Table.ExpandRecordColumn(#"Rows to Records", "ar", List.Distinct(List.Combine(List.Transform(#"Rows to Records"[ar], Record.FieldNames))))
    in
        #"Expanded Records"

  • let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZCxDoAgEEN/xRBHBy2cwA/4E4RNBxP/f1av5oijTn0lvaNQipvc4JZ9O9auEYy8Ubioc3XgRC/jqCIqoPMqLcVJMZqNolEyym07fNB9jwJ3jz4n6GlmEp+b63iKiVdEOtDJK/WnOfghKsGzKvQ5E51cUusJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
        #"Rows to Records" = Table.Group(Source, "Column1", {"ar", each Record.Combine(List.Transform(List.Split(List.Transform(Table.ToRows(Table.RemoveColumns(_, "Column1")), each List.Select(_, each Text.Trim(_)<>"")), 2), each Record.FromList(_{1}, _{0})))}, 0, (x,y) => Number.From(x<>y)),
        #"Expanded Records" = Table.ExpandRecordColumn(#"Rows to Records", "ar", List.Distinct(List.Combine(List.Transform(#"Rows to Records"[ar], Record.FieldNames))))
    in
        #"Expanded Records"

    Screenshot 2021-08-28 110115.png