Power BI/ Power Query: increment the highest number in one sheet based on conditions in another sheet

Question

I am trying to filter the highest number in each of four columns in Power BI.

For specific occurences of a string in the column of another excel file loaded in my Power BI, I want to select one of the four number and increment it.

I have several difficutlies: - I cannot select the highest number - I do not know how to create a costum column referencing another sheet - I do not know how to "store" a value dinamically for each occurence

Here is the input excel workbook:

Here is the excel workbook with data to increment (in Power Bi I already suprressed the strings, seperated my column in four columns each needing to be incremented, becauseI did not know to do it in another way. Having strings character at the begining for example, was a clear issue to increment).

Here is the resulting excel: each time a specific name appears I add one to the higest value of each iteam in my column :

I need to search for the highest number in each of these columns:

When I see "ItalyB", "ItalyZ", "UKY" or "UKM", I need to increment the highest number. The first incrementation will be equal to the highest number from the column + 1, and the second, to the result + 1.

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.

I do not know how to do that. I have added a date:

And I am trying to increment given the date, but I am having difficutlies:

How do these two sheets correlate? What does ItalyB have to do with the first sheet? — Nick.McDermaid
These ranges could be seen as "occurences" happenning in specific zones (four zones). I modified my question to make it clearer. In the first sheet, I look for the "last" and thus "highest" occurence in a region (such as ItalyZ, which correspond to the column "ABE range low") and I keep incrementing the last highest occurence. — Tomas Michel
Is the objective of this to do reporting? Or do you actally need to keep track of these incremented columns over time? Power BI is good for reporting and generating non-permanent read-only attributes, but if you actually need to store these id's for other purposes, you need a database. You could probably do what you want in Power BI but it may be more appropriate to do this in a database. — Nick.McDermaid
Sadly, I do not have access to a database. Each month I have to do this merger and look for the highest value...and thus doing it through Power Query instead of a manual search look would be a massive step forward — Tomas Michel

Param Saraf Param Saraf · Accepted Answer · 2020-03-27T03:34:36

Although I didn't understand your question as it is not explained well (you are directly referring to column codes 'ABE0000' etc. without showing which excel or sheet you are referring)

But I can answer one part which I understood. Create a new column which increments highest number in it by 1.

Lets call the calculated column as ABERangeLowIncrement:

ABERangeLowIncrement = var maxABERangeLow = Calculate(max('ABE Range Low'),ALL(TableName'ABE Range Low'))

return if(TableName'ABE Range Low' = maxABERangeLow,maxABERangeLow +1, TableName'ABE Range Low')

Power BI/ Power Query: increment the highest number in one sheet based on conditions in another sheet

1 Answers