How to group consecutive and interrupted rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JL05NUYrVQWH6F6Tm4RTDJknAGNx6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Valve = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Valve"}, {{"rows", each _, type table [Valve=nullable text]}},GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Group", 0, 1, Int64.Type),
    #"Expanded rows" = Table.ExpandTableColumn(#"Added Index", "rows", {"Valve"}, {"Valve.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded rows",{"Valve.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",each [Group],each if Number.IsEven([Group]) then null else ([Group]+1)/2,Replacer.ReplaceValue,{"Group"})
in
    #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

Hi CornelisV ,

 

Do you have any ID on this values that allow for the sorting?

 

We can do this by adding some steps in this case I have done the following:

• Sort by the ID column
• Add an index column
• Add a new column with the following code:

try if  #"Added Index1"[Valve]{[Index]-1} <> [Valve] and [Valve] = "Open" then 1 else null otherwise null

• Sort rows by the new column and  Index
• Add another index column
• Replace values on the new index column

= Table.ReplaceValue(#"Added Index",each [Index2], each if [Custom] = 1 then [Index2] else if [Valve] = "Closed" then 10000000000 else null ,Replacer.ReplaceValue,{"Index2"})

• Sort by ID
• Do a fill down on the latest column
• Replace the 10000000 on the index column
• Delete the unwanted columns
• 

   

Please see the code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JL05NUdJRMlSK1UHiGoG5/gWpeUCOMaqcCbKcKTLHDFWhOSrXApVriazT0ACFZ4jCM0LVaAh0TywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Valve = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Valve", type text}, {"ID", Int64.Type}}),
    #"Added Index1" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each try if  #"Added Index1"[Valve]{[Index]-1} <> [Valve] and [Valve] = "Open" then 1 else null otherwise null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Descending}, {"Index", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index2", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index2], each if [Custom] = 1 then [Index2] else if [Valve] = "Closed" then 10000000000 else null ,Replacer.ReplaceValue,{"Index2"}),
    #"Sorted Rows1" = Table.Sort(#"Replaced Value",{{"ID", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows1",{"Index2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Filled Down",10000000000,null,Replacer.ReplaceValue,{"Index2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Index", "Custom"})
in
    #"Removed Columns"