Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF + Lookupvalue from partial text

Hi Power User!

 

I have the following tables:

 

TABLE 1

Code 1Description 1Code 2Description 2Code 3
1.10Text 11.10.1Text 11.10.1
1.100Text 21.10.2Text 21.100.2
1.110Text 31.20.1Text 31.110.1.1
1.120Text 41.20.2Text 41.120.2.1.1.1

 

TABLE 2

 

AccountAccount_DescriptionCode 3Description 1
3000100Text 11.10.1 
3000101Text 21.10.2 

 

I would like to insert in the field "Description 1" of table 2 the filed "Description 1 from Table 1" with a lookup value between the code 3 of table 1 and code 1 of table 1.

The main problem is that there is a partial match between code 1 and code 3, code 3 is a higher level of detail. Only the first digit are the same.

I tried with this code :

IF(LEFT(Master_DB[Note Lines Code],5)="1.100","1.100",
IF(LEFT(Master_DB[Note Lines Code],5)="1.110","1.110",
LEFT(Master_DB[Note Lines Code],4).
 
This code not works always because sometimes there are different numbers of charachters. 
Any advice?
 
Thanks

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    i guess ... this approach

    let 
Table2= ... 
tt=Table.AddColumn(Table2, "Code 12", each Table.SelectRows(Table1, (r)=>Text.BeforeDelimiter(r[Code 3],".",2 )=Text.BeforeDelimiter(_[Code 3],".",2))),

 #"Expanded Code 12" = Table.ExpandTableColumn(tt, "Code 12", {"Code 1", "Code 2"}) 
in 
#"Expanded Code 12"

10 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    This is possible to do however I don't understand what is the link between both codes.

     

    So for the example you are giving you are comparing the 1.10 code 3 (first part) with the 1.10 of code 1?

     

    What is the part of the code or how do you compare both values?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Could you please provide the calculation logic of Description 1 in Table 2 and your expected result? Whether the logic is when the value of Code 3 in Table 2 is between Code 1 and Code 3 of Table 1, then return the value of Description 1 of Table 1? Then the final expected result is what the below table display like?

    AccountAccount_DescriptionCode 3Description 1
    3000100Text 11.10.1Text 1
    3000101Text 21.10.2 

    Best Regards

    Rena

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you all guys, I solved the problem. Sorry if I hadn't been clear in explaining the question.

       

      Best

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Thanks for your feedback. It's glad to hear that your problem has been resolved. Could you please mark it as Answered since it has been resolved? Thank you.

        Best Regards

        Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    i guess ... this approach

    let 
Table2= ... 
tt=Table.AddColumn(Table2, "Code 12", each Table.SelectRows(Table1, (r)=>Text.BeforeDelimiter(r[Code 3],".",2 )=Text.BeforeDelimiter(_[Code 3],".",2))),

 #"Expanded Code 12" = Table.ExpandTableColumn(tt, "Code 12", {"Code 1", "Code 2"}) 
in 
#"Expanded Code 12"
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Anonymous , I solved with this code and some changes, thank you for the advice.