Power Query / Power BI increment data at each occurrences with specific ranges

Question

I am trying to combine worksheets from two different workbooks with Power Query and I have trouble doing that.

I do not would like to increment the "ID" column from one workbook given the occurrences of values in another.

The ID column has rows with letter tags : AB or BE. Following these letters, specific numeric ranges are associated. For both AB and BE, number ranges first from 0000 to 3000 and from 3000 to 6000 and then 6001 to 8000 and 8001 to 9999.

I thus have the following possibilities:

From AB0000 to AB3000
From AB3001 to AB6000
From AB6001 to AB8000
From AB8001 to AB9999
From BE0000 to BE3000
From BE3001 to AB6000
From BE6001 to BE8000
From BE8001 to AB9999

Each category match to the a specific item in my column geography, from the other workbook:

From AB0000 to AB3000, it is ItalyZ ABC
From AB3001 to AB6000 it is ItalyB BDE
From AB6001 to AB8000 it is ItalyZ ABC
From AB8001 to AB9999 it is ItalyB BDE
From BE0000 to BE3000 it is UKM ABC
From BE3001 to AB6000 it is UKY BDE
From BE6001 to BE8000 it is UKM ABC
From BE8001 to AB9999 it is UKY BDE

I am thus trying to find the highest number associated to the first AB category, the second AB category, the first BE category, and the second.

I then want to "bring" this number in the other query and increment it each time that matching country is found in the other workbook. For example :

AB356 is the highest number in the first workbook.

Once the first "ItalyB" is found, the column besides writes "AB357". Once the second is "ItalyB" is found, the column besides write "AB358".

Lets say that in the first file that I am getting I have the following data:

  |---------------------|    
  |       Test          |          
  |---------------------|
  |      AB0000         |          
  |---------------------|
  |      AB0001         |            
  |---------------------|
  |      AB3034         |    
  |---------------------|
  |     AB3035          |    
  |---------------------|
  |      AB6005         |    
  |---------------------|
  |      AB6006         |    
  |---------------------|
  |        AB8011       |    
  |---------------------|
  |       AB8012        |    
  |---------------------|
  |       BE0000        |    
  |---------------------|
  |      BE0001         |    
  |---------------------|
  |      BE3091         |    
  |---------------------|
  |      BE3092         |    
  |---------------------|
  |      BE6001         |    
  |---------------------|
  |        BE6002       |    
  |---------------------|
  |      BE6003         |    
  |---------------------|
  |       BE8104        |    
  |---------------------|
  |       BE8105        |    
  |---------------------|

And a second file as follows:

  |---------------------|
  |     ItalyZ ABC      |    
  |---------------------|
  |      ItalyZ ABC     |    
  |---------------------|
  |      ItalyB BDE     |    
  |---------------------|
  |        ItalyZ ABC   |    
  |---------------------|
  |       ItalyB BDE    |    
  |---------------------|
  |      UKM ABC        |    
  |---------------------|
  |      UKY BDE        |    
  |---------------------|
  |     UKM ABC         |    
  |---------------------|
  |       UKY BDE       |    
  |---------------------|
  |       UKY BDE       |    
  |---------------------|
  |        UKY BDE      |    
  |---------------------|
  |       UKY BDE       |    
  |---------------------|

Then my result would be as follows:

      |---------------------|    
      |       Test          |          
      |---------------------|
      |      AB0002         |          
      |---------------------|
      |      AB0003         |            
      |---------------------|
      |      AB3036         |    
      |---------------------|
      |      AB6007         |    
      |---------------------|
      |        AB8013       |    
      |---------------------|
      |      BE0002         |    
      |---------------------|
      |      BE3091         |    
      |---------------------|
      |      BE3093         |    
      |---------------------|
      |      BE6004         |    
      |---------------------|
      |       BE8105        |    
      |---------------------|
      |       BE8106        |    
      |---------------------|
      |       BE8107        |    
      |---------------------|
      |       BE8108        |    
      |---------------------|

I have tried to adapt a query which was more limited in its scope, by adding the category, I thus have a query as follows:

let
    Source = #table({"Prefix", "Seq_Start", "Seq_End","Zone"},
{{"AB",0,2999,"UKM BDE"}, {"AB",3000,4999,"UKM ABC"}, {"AB",5000,7999,"UKY BDE"}, {"AB",8000,9999,"UKY ABC"},{"BE",0,2999,"ITALYZ BDE"},{"BE",3000,4999,"ITALYZ ABC"}, {"BE",5000,7999,"ITALYB BDE"}, {"BE",8000,9999,"ITALYB ABC"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Seq_Start", Int64.Type}, {"Seq_End", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Prefix"}, #"Source data with occurence", {"Unique key Prefix"}, "Source data with occurence", JoinKind.LeftOuter),
    #"Expanded Source data with occurence " = Table.ExpandTableColumn(#"Merged Queries", "Source data with occurence", {"Number"}, {"Number"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Source data with occurence ", each [Number] >= [Seq_Start] and [Number] <= [Seq_End]), 
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Prefix", "Seq_Start", "Seq_End", "Zone"}, {{"NextSeq", each List.Max([Number]) + 1, type number}})
in
    #"Grouped Rows"

Can you mock up a table of what your end result should look like? — Alexis Olson
One of my main issue is that if my "reccords" starts with zero observations (or even one) of, for example, UKY BDE, the count will start a 0 or 1, rather than starting at "5000" for the first observation of UKY BDE and the "5001". — Tomas Michel

bibakcamki bibakcamki · Accepted Answer · 2020-10-31T06:51:14

your query says "Table.Group"

Did you try to COMBINE those two tables? i prefer "Combine and create new one".

Table-1 and Table-2' s headers were different, First i equalized them. Showed in the in the picture. then combined and created new table. Here is code section;

let
    Kaynak = Table.Combine({#"Puantaj CkrEng", #"Puantaj Genel"}),
    #"Filtrelenen Satırlar" = Table.SelectRows(Kaynak, each ([Firma] <> null)),
    #"Yeniden Sıralanan Sütunlar" = Table.ReorderColumns(#"Filtrelenen Satırlar",{"Firma", "Resource ID", "Görev", "Tarih", "Yevmiye", "HaftaBasi"})
in
    #"Yeniden Sıralanan Sütunlar"

Power Query / Power BI increment data at each occurrences with specific ranges

1 Answers