Counting blocks of consecutive numbers -is it possible?

[lazarus1907]

Hi,

I don't mind whether this can be done in M or DAX, as long as it is done without other languages like Python (which are not enabled for me). Is it possible at all to get a list of blocks of consecutive numbers (please see the example diagram below)? I have tried for 2 days straight and I'm not getting anywhere. I am trying to get the table like the one on the right (below) from the table on the left.

Thanks

[ImkeF · Accepted solution]

Sorry @lazarus1907 ,

my bad. Forgot that this technique always compares against the first element in the group and not against the previous.

I'd recommend this approach then instead:

let
  // Replace this Source step by a reference to your table
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "JcqxEQAgCAPAXVJTEBQdhmP/NTxD98VXgTDQHW2FkCkvOeT9zTkpzznynCsnuh8=", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [Ref = _t, Code = _t]
    ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Ref", Int64.Type}, {"Code", Int64.Type}}),
  ToList = List.Buffer(Table.ToRecords(#"Changed Type")),
  AllBucketID = List.Generate(
      () => Record.AddField(Record.AddField(ToList{0}, "CountAll", 0), "CountGroup", 0), 
      each [CountAll] <= List.Count(ToList), 
      each [
        CountAll = [CountAll] + 1, 
        CountGroup = 
          if [Code] = ToList{[CountAll]}[Code] - 1 then 
            [CountGroup]
          else 
            [CountGroup] + 1, 
        Code = ToList{[CountAll]}[Code], 
        Ref = ToList{[CountAll]}[Ref]
      ]
    ),
  SkipInitialElement = List.Skip(AllBucketID),
  #"Converted to Table" = Table.FromList(
      SkipInitialElement, 
      Splitter.SplitByNothing(), 
      null, 
      null, 
      ExtraValues.Error
    ),
  #"Expanded Column1" = Table.ExpandRecordColumn(
      #"Converted to Table", 
      "Column1", 
      {"Ref", "Code", "CountGroup"}, 
      {"Ref", "Code", "CountGroup"}
    ),
  #"Grouped Rows" = Table.Group(
      #"Expanded Column1", 
      {"CountGroup"}, 
      {
        {"Ref", each List.Max([Ref]), type number}, 
        {"Code", each List.Max([Code]), type number}, 
        {"Count", each Table.RowCount(_), Int64.Type}
      }, 
      GroupKind.Local
    ),
  #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows", {"CountGroup"})
in
  #"Removed Columns"