Forum Discussion

Varan_15's avatar
Varan_15
Helper III
3 years ago
Solved

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
  • ImkeF's avatar
    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"