Forum Discussion

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

In a custom column, put following where Table2 is your Lookup table

= try Table2{[C=Text.BeforeDelimiter([A],"-")]}[D] otherwise null

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

Table2 Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lFKyc9LVYrViVZyBvE88xQCivLTi1KLi8GCTiBBiDSYEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t])
in
    Source

Table1 Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUNTRUitUBs4xMwSwnR10LCMvZUddYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Table2{[C=Text.BeforeDelimiter([A],"-")]}[D] otherwise null)
in
    #"Added Custom"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot for your detailed answer, but the two tables that I put were an example (I forget to mention that).

    it's a confidential document so...

    When I paste the code of table 2, the confidential table turned on the example table.

    Could you please help me

    Thanks

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Table2 code is for testing. Just make sure that name of your query is Table2. Don't use Table2 code, only Table1 code. 
      Basically, in both Table1 and Table2, you just need to replace the Source with your Source. And make sure Table2 is named Table2 only.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello again,

         

        I think I am missing something or I don't get something.

        I have again paste only the code of Table1  and got  my table turned to the example table.

        PS: I am not sure what do you mean by "Replace the Source with your Source"

         

        Thank you

        Best regards