Search code examples
excelpowerbivlookupincrementpowerquery

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

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:

input

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).

inout 2

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

OUTPUT

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

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.

enter image description here

I do not know how to do that. I have added a date: enter image description here

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

Solution

  • 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')