Adding a letter to text in a column|Power Query|Excel Forum|My Online Training Hub

I have a file with a fairly large table (40.000 rows and 65 columns).

Background

The location where this file is stored has 2 sub-folders which contains files that are updated regularly. Each file contains a single table, one has the same 65 columns but 1,000 or so fewer rows and the other table is just 3 columns but about 41,000 rows.

I have power query working to extract the table data from the most recent file in each sub-folder loaded as connection only, then do a Left Outer merge, of the first (larger) table with the smaller table, to combine the tables and get the current table. A sample table of the resulting merged table is attached.

Requirements

I need PQ to look at all rows and where there are matching values in the V7 column to then look at the Transmission column for those rows (any matching V7 values will always be in consecutive rows, this is handled within the query that creates the table).

Then, according to what is in the  Transmission column add a letter (A, B, C, D, E, F etc.) to the text in the CatCode column, according to the following rules

1. 1. There can be 0 or more "MTM ?Sp" values, but each will always have a different number before the "Sp"    (3,  4, 5, 6 or 7). Each needs a letter adding to CatCode, "A" for the first, "B" for the second, "C" for the third and so on.
   2.  After the "MTM" there can be up to 4 different values, always in the following order "ATM", "Semi", "CVT"  and "Direct". For each of these CatCode needs a letter adding to the text, the first letter needs to be whatever comes after the last letter added for the "MTM" (if no "MTM" then start with "A").
   3.  If there are duplicates of the "ATM", "Semi" etc., (any duplicates will always be in adjacent rows) then only the first of the duplicates needs a letter adding  to CatCode and the duplicate(s) need the entire row removing from the table (I did manage to figure out that the best way to do that is to add the correct letter to the first and add a "Z" to the rest of the duplicates and filter out all CatCodes ending with "Z" from the table at the end).

What I have tried

I am sure I was trying along the right lines by using Grouping looking at all rows, then using Text.Join to add the letter to the CatCode. I sort of got that to work but could not think of a way to increase the letter from "A" to "B" etc for consecutive rows where letters needed to be added to CatCode. I knew I had to use Character.FromNumber(number as nullable number) as nullable text, but could not find a way to increase the "number as nullable number" for 65 (which is A) to 66 ("B") for the next row then 67 ("C") for the next etc.

I did write a VBA script to do this (user clicks a button to "RefreshAll", then when the Table changes that will fire the macro that adds the letters etc.). However, I would much prefer it if the user just had to do a "RefreshAll".

In the attached Sample File the first table is what is created by the merging of the 2 tables, the second table shows the desired result and the 3rd table the result of running the VBA.

I have included the VBA with the file so anybody can look at that to get a better understanding of what is meant to happen. 

Though I suspect the VBA code will not help Riny much! 

Thanks for looking at this.