Forum Discussion
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
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"
9 Replies
Hello Varan_15 ,
make table in pivot column table Go to power query.Hi Mahesh0016 ,
i have tried pivot column/ group by/transapose only one input column appearing but as per logic i need input 1 and input 2 (2 columns should be created as per respective date).
whatever available in process column all should be created with separate columns. example
Hi Varan_15 ,
first you have to unpivot the last columns, then merge some columns to create the new headers before pivoting again. Have described that method here: Pivot multiple measures and columns in Power Query (thebiccountant.com)Hi ImkeF ,
Thanks for the update , I'm facing error because of i have 2 inputs entry IN/OUT so i need both entries when we tried your steps it's considered only one input IN/OUT
couldn't get second input entries.
please suggest how to get all the process rows dates as mentioned expected output columns.
Thanks,
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"
