Assign Index (counter) to Boolean Region

[Anonymous]

Sorry if this is a repeat, but I would like to make a new column, "region count" as bellow based on a Boolean value

mask    region count

True          1

False         0

True          2

True          2

False         0

False         0

True          3

True          3

True          3

False         0

True          4

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this M Solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgkKdVWK1YlWcnP0CYaw4EJY5PCpwmlULAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [mask = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"mask", type logical}}),
    #"Added Index1" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Grouped Rows" = Table.Group(#"Added Index1", {"mask"}, {{"ALL", each _, type table}},GroupKind.Local),
    FilteredRowsTrue = Table.SelectRows(#"Grouped Rows", each ([mask] = true)),
    AddedIndex = Table.AddIndexColumn(FilteredRowsTrue, "Region Count", 1, 1),
    FilteredRowsFalse = Table.SelectRows(#"Grouped Rows", each ([mask] = false)),
    AddedCustom = Table.AddColumn(FilteredRowsFalse, "Region Count", each 0),
    #"Appended Query" = Table.Combine({AddedCustom, AddedIndex}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Appended Query", "ALL", {"Index"}, {"Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded ALL",{{"Index", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Index", "mask", "Region Count"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"})
in
    #"Removed Columns"