Reference field's place in a column in Power Query like Excel?

Question

I know this type of question gets asked a lot but I can't adapt the other solutions to meet my needs here. What I'm trying to accomplish is this:

1. I've run a query that maps new data classifications to existing rows. My source dataset is already indexed.
2. There are some rows that the basic query can't match so I've split those into a new query and filtered out everything that's already matched
3. Using a fuzzy merge without a match limit, the query obviously duplicates the rows it matches, including the original index number

Before I merge the second query with the duplicate rows, I need to preserve the original index number for the first instance of the row, then apply the continuation of the index to the duplicated rows. I can do this easily enough in excel:

Where the original index number is [Database Control Number] and the continuation of the index is [New DBCN]

The formula in Excel is really straight forward:

=IF(COUNTIF($AA$2:$AA10,[@[Database Control Number]])>1,MAX(1787,MAX($AF$2:$AF9)+1),[@[Database Control Number]])  

Where, relative to the position of the cell, it

1. checks of there is more than 1 instance of the [Database Control Number] from the top of the column to the current row ,
2. If so, it adds 1 to the highest value found above it in [New DBCN],
3. If not, then it copies the number in [Database Control Number]

The goal then would be to merge the original list to this one leaving out matching from the original. In this way I would be preserving the original items and adding the new items all with completed data and unique [Database Control Number].

Is there a way to accomplish this in Power Query M?

Answer 1

I would recommend grouping by Database Control Number, sorting the data within each group, adding an index column, and then copying or applying the increment based on the current row's index. (The previous row's data can be looked up by decrementing the current row's index.)