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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted

 

using an auxiliary index column, the V element of the table.group function can dig the spider out of the hole.

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc3LDcAwCATRXjjn4AV/a7HcfxtJBt/eSGjZ22SPSWHn2ea44sANV9xxwwN3PPHAC8/P7rmzcO6oEDkkEbkkJ3JKQeSW/udR71kj7lkn7tn/P1rJmIQyFuF2zgs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna2", Int64.Type}}),
   aci = Table.AddIndexColumn(#"Modificato tipo", "Indice", 0, 1, Int64.Type),
    #"Modificato tipo1" = Table.TransformColumnTypes(aci,{{"Colonna2", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo1", {"Colonna2", "Indice"}, {{"last", each Table.Last(_)},{"Conteggio", each Table.RowCount(_), Int64.Type}}, GroupKind.Local,(x,y)=>-x[Colonna2]+y[Colonna2]-(y[Indice]-x[Indice])),
    #"Tabella ref espansa" = Table.ExpandRecordColumn(#"Raggruppate righe", "last", {"Colonna1", "Colonna2"}, {"ref", "code"}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Tabella ref espansa",{"Colonna2", "Indice"})
in
    #"Rimosse colonne"

 

 

PS

questa soluzione presuppone che la tabella sia ordina in senso crescente nella colonna Code.

 

 

View solution in original post

19 REPLIES 19
Highlighted
Super User III
Super User III

Hi @lazarus1907 ,

there is a magical 5th parameter in the Table.Group function that allows for just this kind of logic: 

https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe... 

 

let
// Replace this Source step by a reference to your table
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "i45WMlTSUTI0MFCK1YlWMgKzDcFsYzDbGMw2AbNNlGJjAQ==", 
                  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}}),
  #"Grouped Rows" = Table.Group(
      #"Changed Type", 
      {"Code"}, 
      {{"Ref", each List.Max([Ref]), type nullable number}, {"Count", each List.Count(_)}}, 
      // Don't skip the following parameter, as the logic would break otherwise:
      GroupKind.Local, 
      // This is where the magic happens: 
      // The difference between the current Code value and the previous will be calculated and and a new group will be created if it is not equal to 1 (non consecutive)
      (x, y) => Number.From(y[Code] - x[Code] <> 1)
    )
in
  #"Grouped Rows"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted

Brilliant answer, Imke, thanks a lot! Something is not quite right with your code, because I never get groups larget than 2, but I was unaware of these options, so maybe I can find the solution myself and learn in the process. I'll check your site carefully -it's not the first time I find useful stuff there!

Highlighted

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Hi @lazarus1907 

 

You could also try this approach:

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}}),

    // 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 ((c - List.Last(s){0}?)??1) <= 1
            then List.RemoveLastN(s, 1) & {{c, ((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"

 

Best,

Spyros




Feel free to connect with me:
LinkedIn

Highlighted

Interesting code! However, I get the error "Token RightParen expected". Could you fix it for me?
Thanks

Highlighted
Solution Specialist
Solution Specialist

@lazarus1907 not sure who you're replying to, but I just tested both @ImkeF 's code and mine and they both work in the latest PBI Version



Feel free to connect with me:
LinkedIn

Highlighted

 

using an auxiliary index column, the V element of the table.group function can dig the spider out of the hole.

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc3LDcAwCATRXjjn4AV/a7HcfxtJBt/eSGjZ22SPSWHn2ea44sANV9xxwwN3PPHAC8/P7rmzcO6oEDkkEbkkJ3JKQeSW/udR71kj7lkn7tn/P1rJmIQyFuF2zgs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna2", Int64.Type}}),
   aci = Table.AddIndexColumn(#"Modificato tipo", "Indice", 0, 1, Int64.Type),
    #"Modificato tipo1" = Table.TransformColumnTypes(aci,{{"Colonna2", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo1", {"Colonna2", "Indice"}, {{"last", each Table.Last(_)},{"Conteggio", each Table.RowCount(_), Int64.Type}}, GroupKind.Local,(x,y)=>-x[Colonna2]+y[Colonna2]-(y[Indice]-x[Indice])),
    #"Tabella ref espansa" = Table.ExpandRecordColumn(#"Raggruppate righe", "last", {"Colonna1", "Colonna2"}, {"ref", "code"}),
    #"Rimosse colonne" = Table.RemoveColumns(#"Tabella ref espansa",{"Colonna2", "Indice"})
in
    #"Rimosse colonne"

 

 

PS

questa soluzione presuppone che la tabella sia ordina in senso crescente nella colonna Code.

 

 

View solution in original post

Highlighted

you could also try using Table.FuzzyGroup on your Code column 🙂

 

 

 

Highlighted

 

a solution (?) using only the mouse, without any "esoteric" function

 

 

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc3LDcAwCATRXjjn4AV/a7HcfxtJBt/eSGjZ22SPSWHn2ea44sANV9xxwwN3PPHAC8/P7rmzcO6oEDkkEbkkJ3JKQeSW/udR71kj7lkn7tn/P1rJmIQyFuF2zgs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna2", Int64.Type}, {"Colonna1", Int64.Type}}),
   aci = Table.AddIndexColumn(#"Modificato tipo", "Indice", 1, 1, Int64.Type),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(aci, "diff", each [Colonna2]-[Indice]),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata", {"diff"}, {{"Conteggio", each Table.RowCount(_), Int64.Type}, {"code", each List.Max([Colonna2]), type nullable number}, {"ref", each List.Max([Colonna1]), type nullable text}})
in
    #"Raggruppate righe"

 

 

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