Unstacking Data

[KDS]

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.

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

... and so on.

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

Location	Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7	Field 8	Field 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!

[Syndicate_Admin]

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"

[CNENFRNL]

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"

[jennratten · Accepted solution]

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]

@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]

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

[KDS]

Thank you - really appreciate it.