Forum Discussion

jaimemagana's avatar
jaimemagana
New Member
4 years ago
Solved

Vlookup - PowerBI - Text range or list

Hi there!

 

I'm trying to obtain the license plate for a number of vehicles, based on how do their license pates end.

 

Original lookup table:

 

YearJanFebMarAprMAyJunJulAugSepOctNovDic
2022            
2021LMLLMXLNNLPDLPWLRPLSFLSPLTDLTPLVDLVV
2020LFHLFYLGGLGHLGPLHGLJDLJRLKFLKVLLJLMC
2019KTJKVBKVXKWTKXRKYNKZKKZYLBNLCGLCYLDR
2018KHGKHYKJVKKRKLNKMMKNKKPDKPSKRJKRZKSS
2017JWNJXFJYBJYTJZPKBMKCHKCTKDHKFCKFWKGN

 

(I did no copy the whole thing, for it is not necessary, that dates back to 2000, because it's not necessary)

 

When unpivoting the table to work with it, we obtains something like this (simplified for year 2021):

 

tbLookupPlateYear

YearMonth3LettersMonthNumber
2021DicLVV12
2021NovLVD11
2021OctLTP10
2021SepLTD09
2021AgoLSP08
2021JulLSF07
2021JunLRP06
2021MayLPW05
2021AbrLPD04
2021MarLNN03
2021FebLMX02
2021JanLML01

 

Now, lets obtain the plate date:

 

Example plate A:   E 1234 LSF

the date would be July 2021

 

Example plate B :  E 4321 LMM

The date would be between january and february 2021, so we state the lowest, january 2021

 

How can we do this in PowerBI? In Excel you would use vlookup, with no exact match, and it would work just fine. Not in PowerBI, that only outputs exact matches. 

 

Any ideas?

 

 

 

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi jaimemagana ,

     

    You can convert the text to UniCode and then compare them.
    1. First add a new column to table.

     

    NumberCode = 
UNICODE ( 'Table'[3Letters] ) * 10000
    + UNICODE ( MID ( 'Table'[3Letters], 2, 1 ) ) * 100
    + UNICODE ( RIGHT ( 'Table'[3Letters] ) )

     

    2. Then create the column in plate table.

     

    Lookup = 
VAR Fir = UNICODE ( [3Letters] )
VAR Sec = UNICODE ( MID ( [3Letters], 2, 1 ) )
VAR Thir = UNICODE ( RIGHT ( [3Letters] ) )
VAR PlateUnicode = Fir * 10000 + Sec * 100 + Thir
VAR Tab =
    FILTER (
        'Table',
        'Table'[NumberCode]
            = MAXX ( FILTER ( 'Table', [NumberCode] <= PlateUnicode ), [NumberCode] )
    )
RETURN
    MAXX ( Tab, [Attribute] ) & " " & MAXX ( tab, [Year] )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi jaimemagana ,

     

    You can convert the text to UniCode and then compare them.
    1. First add a new column to table.

     

    NumberCode = 
UNICODE ( 'Table'[3Letters] ) * 10000
    + UNICODE ( MID ( 'Table'[3Letters], 2, 1 ) ) * 100
    + UNICODE ( RIGHT ( 'Table'[3Letters] ) )

     

    2. Then create the column in plate table.

     

    Lookup = 
VAR Fir = UNICODE ( [3Letters] )
VAR Sec = UNICODE ( MID ( [3Letters], 2, 1 ) )
VAR Thir = UNICODE ( RIGHT ( [3Letters] ) )
VAR PlateUnicode = Fir * 10000 + Sec * 100 + Thir
VAR Tab =
    FILTER (
        'Table',
        'Table'[NumberCode]
            = MAXX ( FILTER ( 'Table', [NumberCode] <= PlateUnicode ), [NumberCode] )
    )
RETURN
    MAXX ( Tab, [Attribute] ) & " " & MAXX ( tab, [Year] )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.