Forum Discussion
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 disp...
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
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"