Forum Discussion

Cul33's avatar
Cul33
Frequent Visitor
8 years ago
Solved

Assigning values to increments in a loop

I have a column of data comprising blocks of 3 entries.  Each block of 3 is unique and I need to add a new column which contains n an incremental value for each of the entries within each block as A,...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi Cul33

     

    In Power query, I would

     

    1. Add an index to the core table
    2. take a copy of the table.
    3. group the copied table by the first three columns - adding a MIN aggregation column
    4. Merge the grouped table back to the original table including the new [offset] column
    5. create a NEW column that subtracts the offset from the index to generate a number that can be converted to ABC
    6. remove the interum columns. 

     

Cul33's avatar
Cul33
Frequent Visitor
8 years ago

Hi Phil

 

Thanks for the reply.  

 

Below is a sample (which is extracted from the preliminary stages of transformation steps, hence the data types are not applied).

 

Each value corresponds to a heading that I want to create, which could be A, B or C.   So for the first block of 3 entries, in rows 67 to 69 -  the first entry for period Jan 2015 to Dec 2015 I want to be able to assign a value of "A" in an adjacent column, then for the second entry  a value of "B" and the third a value of "C".  When a change in Period is identifed, i.e. in row 70, I am wanting to return a value of A again in the adjacent and then to loop through to C - which would repeat for several thousand rows

.

 

I hope this makes sense.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi Cul33

 

In Power query, I would

 

  1. Add an index to the core table
  2. take a copy of the table.
  3. group the copied table by the first three columns - adding a MIN aggregation column
  4. Merge the grouped table back to the original table including the new [offset] column
  5. create a NEW column that subtracts the offset from the index to generate a number that can be converted to ABC
  6. remove the interum columns. 

 

  • Cul33's avatar
    Cul33
    Frequent Visitor
    8 years ago

    Thanks very much Phil - greatly appreciated. That addressed the issue.

     

    If this were being done in Excel it woud lend itself well to an If function where, if the period is the same add 1 and if it changes return a value of 1 again. That would depend on the first entry having a value of 1 of course.   A nested If / VLookup / Match Index could then return the required Heading according to the value returned.  

     

    I wonder therefore if there is a way to accompish this with DAX rather than creating a table?