Forum Discussion
KDS
Helper I
5 years agoUnstacking 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 disp...
- 5 years ago
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
jennratten
Super User
5 years agoHi! 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
- KDS5 years ago
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!
- jennratten5 years ago
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- KDS5 years ago
Helper I
Thank you - really appreciate it.