Power BI Costum Column incrementing data in one sheet based on values in another sheet Text.Combine

[Tom224]

I am using Power BI to get data from a file, and look in another file. Based on specific conditions (four), I need to look at my antoher file, and increment specific ID, which I like to append to the matching columns.
I am not trying to merge the files, but only to look for information in one, and then add a number.
Here are my four conditions: - ItalyZ - ItalyB - UKY - UKM
In my other file, I am looking in the ID colum, and searching for these amount:

• The highest number between ABE0000 and ABE4000, once found, I add one to AB for each "ItalyZ" elements. i.e AB0003 for one element "ItalyZ", the next "ItalyZ" wil get AB0004
• The highest number between ABE4000 and ABE9000, once found, I add one to AB for each "ItalyB" elements. i.e AB4003 for one element "ItalyB", the next "ItalyB" wil get AB0004
• The highest number between BC0000 and BC4000, once found, I add one to BC for each "UKY" elements. i.e BC0003 for one element "UKY", the next "UKY" wil get BC0004
• The highest number between BC4000 and BC9000, once found, I add one to for each "UKM" elements. i.e BC4003 for one element "UKM", the next "UKM" wil get BC4004

Here my input files:

I have been tying nested IF and "Table." in order to look in other sheet, but I am having difficulties. My main difficulty is that it is in another sheet. Selecting the letters, breaking the columns and then increment for specific condition would be ok but the fact that this located in another file is an issue.


I have started moving in this direction for example:

If [#"Geography "] = UKM, Table.Combine
In 
Text.Combine( 
    Table.Combine(
        Table.SelectRows(Shee1,
        each Text.Contains(AB

What could I do to solve this ?