Forum Discussion

ahrvanovic's avatar
ahrvanovic
Frequent Visitor
5 years ago
Solved

Transforming Data - help needed

I need to transform  data from table 1 to table 2. What is the best way to do that?    Thank you.  Source table: https://printgs-my.sharepoint.com/:x:/g/personal/ahrvanovic_printgs_o...
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI ahrvanovic ,

    Please run the following query.
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BDoQwCEXvwrqLQutUuYVxo9O4mBOMMd4/EkhJddE0/Jf/oFZACLDKW67feckfOUbYQwVy8D80R8vTq4AN5GcBmSwfZNi6ArUNn5fJQfGGmlLLx+eG3PJJhm8nSpwMyGUB5o5kpsEIOlFX4VwMkMsUjNrYbw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Operation = _t, Type = _t, Time = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Type", "Operation"}, {{"Data", each _, type table [ID=nullable text, Operation=nullable text, Type=nullable text, Time=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "order",1)),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Time", "order"}, {"Custom.Time", "Custom.order"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom1",{"Data"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Custom.order", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each [Operation]&"-"&[Custom.order]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Time", type time}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Type]), "Type", "Custom.Time"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Start", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Custom.order", "Custom"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "NewID"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"NewID", "Operation", "Start", "Stop"})
    in
    #"Reordered Columns"