Performing case sensitive lookup in power bi

[uc]

Hello

I am having issues while doing lookup because the lookup value is case sensitive in Power BI. I will explain it with an example. Please see the example below. Any ideas is much appreciated. THanks.

Table 1

Code	Colour
AxP	White
tmu	Blue
TMu	Black

Table 2

Item	Code	Colour (I need to get the right colur by doing a lookup from the code column in table 1)
Lamp	TMu	Black
Table	tmu	Blue
Mug	AxP	White

[MasonMA · Accepted solution]

@uc 

Hi, i'd suggest making them consistent first in Power Query. 

For example in Power Query add one more step to both Table1 and Table2, then do a merge on "Code". 

= Table.TransformColumns( #"PreviousStep", {{"Code", Text.Upper, type text}} // or Text.Lower )

If you wanted to skip Power Query transformation, try this with DAX: 

LOOKUPVALUE( Table1[Colour], UPPER(Table1[Code]), UPPER(Table2[Code]) )

[uc]

Thankyou. It works.

[johnt75 · Accepted solution]

This is not possible in DAX as the values are stored in a case insensitive way. To accomplish it you will need to perform transformations before the data is loaded. Ideally you would do something at the source but it is also possible in Power Query, but doing it in Power Query would significantly slow down data refresh if you have a lot of data.

The approach I would take, either in Power Query or in SQL, would be add a unique integer identifier to table 1. In Power Query you could add an Index column for this purpose.

The query pulling table 2 would then need to map the existing code value to the integer and store the integer. You can then use the integer columns to create a relationship, look up values etc.

[uc]

Thanks Mason and John for the idea.

[MasonMA]

This is correct, thanks for pointing this out 

[tayloramy]

Hi @uc, 

I agree with @MasonMA. 

Normalize your values in PQ and then don't waste run-time compute on tyring to do case sensitive nonsense. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this as the solution. 

[MasonMA · Accepted solution]

@uc 

Hi, i'd suggest making them consistent first in Power Query. 

For example in Power Query add one more step to both Table1 and Table2, then do a merge on "Code". 

= Table.TransformColumns( #"PreviousStep", {{"Code", Text.Upper, type text}} // or Text.Lower )

If you wanted to skip Power Query transformation, try this with DAX: 

LOOKUPVALUE( Table1[Colour], UPPER(Table1[Code]), UPPER(Table2[Code]) )