Rank the string according to the highest rank value in it.

[Lobo1908]

Hi, 

I have a lookup table with a list of codes and a rank assigned against each code.
The lookup table looks as follows:

Code	Rank
DI9001	1
DI2102	1
DI0303	2
DI0404	2
DI6005	3
DI5006	3
DI0307	3
DI0018	4
DI0019	4
DI0020	4

I also have a data table with a string of codes assigned to parts. 

Please see below:

Part #	Codes
Part 1	DI9001 DI2102 DI6005
Part 2	DI9001 DI0020 DI5006
Part 3	DI9001
Part 4	DI5006
Part 5	DI0307
Part 6
Part 7	DI9001 DI0020
Part 8	DI9001 DI0303
Part 9	DI0307 DI0020
Part 10	DI0020 DIV090

Please note that in the string there are values that should be ingored as they are not present in the lookup table.

In this eg it is DIV090.

What I'm looking for is for power bi to review the stirng of codes and assigned rank to the part based on the highest ranked code in the string according to the lookup table

Solution should look as follow.

Part #	Codes	Rank
Part 1	DI9001 DI2102 DI6005	3
Part 2	DI9001 DI0020 DI5006	4
Part 3	DI9001	1
Part 4	DI5006	3
Part 5	DI0307	3
Part 6
Part 7	DI9001 DI0020	4
Part 8	DI9001 DI0303	2
Part 9	DI0307 DI0020	4
Part 10	DI0020 DIV090	4

Could someone support please.

[FreemanZ · Accepted solution]

hi @Lobo1908 

the solution consists 3 steps:

1) split the codes column to rows with Power Query, 

2) connect the splitted table with the lookup table

3) get the expected RankTable

here comes the details:

1) split to rows

2) connect with the lookup table

3) create a new table with this:

RankTable =
ADDCOLUMNS(
    VALUES(TableName[PartNo]),
    "Rank",
    CALCULATE(
        MAXX(
            TableName,
            RELATED(Lookup[Rank])
        )
    )
)

the result looks like this: