Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping multiple rows

Hello,   I have a device that will write data into a table whenever an event occurs. An event means either the device is turned on or turned off, or an abnormality happened or the abnormality disap...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Anonymous  

    you add a column where you check if "status" = "Off". Then use a an index-column like "Event" with unique values otherwise (a real null. That allows you to fill down the values found for the Off-values.

    But as this will not return a continuous series, there is another version where you group on that new column and add an index on the grouped data instead.

     

    Paste the following coded into the advanced editor and follow the steps:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpPS1OK1YlWMgKx88BMYwTTBEmFKULYDEnYHCFsAWXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Cycle", each if [Status] = "off" then [Event] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Cycle"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Event", Int64.Type}, {"Status", type text}})
in
    #"Changed Type"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcpPS1OK1YlWMgKx88BMYwTTBEmFKULYDEnYHCFsAWXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Status = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Cycle", each if [Status] = "off" then [Event] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Cycle"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Event", Int64.Type}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cycle"}, {{"CyclePartition", each _, type table [Event=number, Status=text, Cycle=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded CyclePartition" = Table.ExpandTableColumn(#"Added Index", "CyclePartition", {"Event", "Status"}, {"Event", "Status"})
in
    #"Expanded CyclePartition"