Forum Discussion
KDS
4 years agoHelper I
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.
| 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!
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
- jennrattenSuper User
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- KDSHelper 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!
- jennrattenSuper 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
- CNENFRNLCommunity 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" - Syndicate_AdminAdministrator
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"