cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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

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.

 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

1 ACCEPTED SOLUTION
Super User

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 =
VALUES(TableName[PartNo]),
"Rank",
CALCULATE(
MAXX(
TableName,
RELATED(Lookup[Rank])
)
)
)

the result looks like this:

Super User

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 =
VALUES(TableName[PartNo]),
"Rank",
CALCULATE(
MAXX(
TableName,
RELATED(Lookup[Rank])
)
)
)

the result looks like this:

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors