Forum Discussion
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,...
Hi Cul33
In Power query, I would
- Add an index to the core table
- take a copy of the table.
- group the copied table by the first three columns - adding a MIN aggregation column
- Merge the grouped table back to the original table including the new [offset] column
- create a NEW column that subtracts the offset from the index to generate a number that can be converted to ABC
- remove the interum columns.
Hi Cul33
In Power query, I would
- Add an index to the core table
- take a copy of the table.
- group the copied table by the first three columns - adding a MIN aggregation column
- Merge the grouped table back to the original table including the new [offset] column
- create a NEW column that subtracts the offset from the index to generate a number that can be converted to ABC
- remove the interum columns.
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?