Forum Discussion
Varan_15
3 years agoHelper III
convert row to columns
Hi All, I have data single ID contains mutiple rows how to convert into columns example input file like : can we use summarize functions ? Expected Result: Thanks
- 3 years ago
Hi Varan_15 ,
sure, please paste the following code into the advanced editor of a new blank query and follow the steps:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDRU0lHKzCsoLQHShvqW+kZGCoYmVqYWYK6hAYhvaWVqqhSrg1BflJqVmgzRYATWYGBlbATmmoG5xlYmBigaEBYgKUBwgfaZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Process = _t, IN = _t, OUT = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Process", type text}, {"IN", type datetime}, {"OUT", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Process"}, {{"Partition", each _, type table [ID=nullable number, Process=nullable text, IN=nullable datetime, OUT=nullable datetime]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition],"Index", 0,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Partition"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "IN", "OUT", "Index"}, {"ID", "IN", "OUT", "Index"}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Custom", {"IN", "OUT"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Only Selected Columns", {{"Index", type text}}, "en-US"),{"Process", "Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Header"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Header]), "Header", "Value") in #"Pivoted Column"
ImkeF
3 years agoCommunity Champion
Hi Varan_15 ,
sure, please paste the following code into the advanced editor of a new blank query and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDRU0lHKzCsoLQHShvqW+kZGCoYmVqYWYK6hAYhvaWVqqhSrg1BflJqVmgzRYATWYGBlbATmmoG5xlYmBigaEBYgKUBwgfaZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Process = _t, IN = _t, OUT = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Process", type text}, {"IN", type datetime}, {"OUT", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Process"}, {{"Partition", each _, type table [ID=nullable number, Process=nullable text, IN=nullable datetime, OUT=nullable datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition],"Index", 0,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Partition"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "IN", "OUT", "Index"}, {"ID", "IN", "OUT", "Index"}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Custom", {"IN", "OUT"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Only Selected Columns", {{"Index", type text}}, "en-US"),{"Process", "Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Header"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Header]), "Header", "Value")
in
#"Pivoted Column"