cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Counting blocks of consecutive numbers -is it possible?

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.

borra.png

Thanks

19 REPLIES 19
Highlighted

@Smauro 

The version we have to use at work is from October 2019, and I'm definitely getting an error (please see below). Is that double question mark a new feature? I'm not familiar with it.borra.png

Highlighted

@Rocco_sprmnt21
Thanks for your code. For some reason, I'm getting an error. Could it be because my version is from October 2019?

borra.png

Highlighted

@Rocco_sprmnt21I just had to remove some "Int64.type" which my version doesn't like, and both of your attemps work like pure magic! Amazing!

Highlighted
Solution Specialist
Solution Specialist

@lazarus1907  thought so, no worries.

?? is a null coalescing operator: a shorthand for "if x = null then y else x"

 

Try this one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc3BDQAhCETRXjh7AFxQazH238bKjLf3wyTsLSZNzLqctsXhD+5wwB+ccMADzmtX7gfM/YS5X9cREzZFLEZ9jlSGI4zREc6o56nvEoh3qfejLucH", 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}}),

    // NullCheck = helper function for dealing with nulls
    NullCheck = (check as any, return as any) as any => if check = null then return else check,

    // Get codes and find the last consecutive one, counting consecutives in the process:
    #"Codes and Counts List" = List.Accumulate(List.Sort(#"Changed Type"[Code]), {},
        (s, c) =>
        if NullCheck((c - List.Last(s){0}?), 1) <= 1
            then List.RemoveLastN(s, 1) & {{c, NullCheck((List.Last(s){1}?), 0) + 1}}
        else s & {{c, 1}}),
    #"Codes and Counts to Table" = Table.FromList(#"Codes and Counts List", (l) => {l{0}, l{1}}, {"Code", "Count"}),

    // Keep only the last of the consecutive codes
    #"Keep Last Codes" = Table.Join(#"Changed Type", {"Code"}, #"Codes and Counts to Table", {"Code"}, JoinKind.Inner)
in
    #"Keep Last Codes"

 




Feel free to connect with me:
LinkedIn

Highlighted

Hi @lazarus1907 ,

 

have you tried the third method I suggested?
I'm curious to know if it is useful for you.
Certainly it depends on the specific content of the column of consecutive ids to be grouped, but perhaps with the appropriate calibrations it could be a third way ...

Highlighted

Oh, I tried both and I found them incredibly useful. Now I am doing things I didn't know I could do before, and yes, I sorted out my original problem at work. Thanks.

Highlighted

"both" means two, but I give three suggestions.

Highlighted

@Rocco_sprmnt21If you are referring to the "fuzzy" option, I'm planning to check it at some point, but I'm fairly happy with any of the other two for the moment.

Highlighted
Solution Supplier
Solution Supplier

Hi, @lazarus1907 

You can also try my coding solution, which can handle about 12,000 lines of data.

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("Tc+xDYAwDAXRXVJTJA42hJYNaBEdoWUAxO5QEB+d9WSd9NcrLPUIU+rCfO71PVK+uw8F7B0zqI49aI4KDo7WUCLNAaQ5gjRLQ9XRMUW0oL5JLaKCJjSjgvosi79fReXeHg==", BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source)&{{}},
    acc = List.Accumulate(
              rows, 
              {{}, {}, 0}, 
              (s,c)=>if s{0}={} then {c&{1}, s{1}, 1} 
                     else if s{0}{1}+1=c{1}? then {c&{s{2}+1}, s{1}, s{2}+1} 
                          else {c&{1}, s{1}&{s{0}}, 1}
          ){1},
    result = Table.FromRows(acc, {"Ref", "Code", "Count"})
in
    result
Highlighted
Frequent Visitor

Hi @lazarus1907 , 

I got a simpler solution, but there is also limitations, depends on the complex of your data.  You can try the formula to add a new column, this column will help you to identify the same group, then you can group this new column and count the row number.  I have added several rows addtionally to test the formula and it seems ok.  

 

Table.AddColumn(
    #"Changed Type", 
    "Custom.3", 
    each Number.ToText(
        List.Max(Table.SelectRows(#"Changed Type", (x) => x[Code] - [Code] = x[Ref] - [Ref])[Code])
      )
      & Number.ToText(
        List.Max(Table.SelectRows(#"Changed Type", (x) => x[Code] - [Code] = x[Ref] - [Ref])[Ref])
      )
  )

 

 

RefCodeCustom.3

11131175
21141175
31151175
41161175
51171175
62042068
72052068
82062068
955856213
1055956213
1156056213
1256156213
1356256213
1460160215
1560260215
1611511516
1711411417
1811311520
1911311319
2011511520
2111711721

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors