Forum Discussion
Transforming Data - help needed
- Anonymous5 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"
I dont think so.
Same operation must start and end after.
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"
- ahrvanovic5 years agoFrequent Visitor
Thank you very much.
- Anonymous5 years agoNot applicable
Happy to help! 🙂