ifna formula in Power Query - replace multiple/alternate vlookup

[blackduck]

Hi,
I have some formula's that I am trying to replace using Power Query.... not sure what the best way to explain this is but I will explain the scenario in excel first. I need to look at column 1 and return the value specified in the lookup table, but if the number doesn't exist in column 1 lookup table, I want to lookup column 2 and return the value based on the lookup table. The formula I am using to do this is: =IFNA(VLOOKUP(A2,A10:C15,3,0),VLOOKUP(B2,B10:C15,2,0))

EXAMPLE DATA with last column being what i want to be generated by the formula:
[TABLE="class: cms_table, width: 303"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]RETURNED VALUES[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]d[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]e[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]f[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]f[/TD]
[TD]black

[/TD]
[/TR]
</tbody>[/TABLE]


LOOKUP TABLE:
[TABLE="class: cms_table, width: 281"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]d[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]e[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]f[/TD]
[TD]black
[/TD]
[/TR]
</tbody>[/TABLE]



Does anyone have any ideas how to do this in Power Query? And can Power Query return a certain value if no matches are received at all? I know how to merge tables as a replacement for standard vlookups but have no idea how to do a complicated lookup. Any help would be greatly appreciated!!!!!

 

[VBA Geek]

You could do a double merge and then test which returned table is empty?

let
    DataTable = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],


    LookupTable = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],


    Merge1 = Table.NestedJoin(DataTable,
                              "Column 1",
                              LookupTable,
                              "Column 1",
                               "Merge1",
                               JoinKind.LeftOuter
                              ),


    Merge2 = Table.NestedJoin(Merge1,
                              "Column 2",
                              LookupTable,
                              "Column 2",
                              "Merge2",
                               JoinKind.LeftOuter
                             ),


    FinalTable = Table.AddColumn(Merge2,
                                 "RESULT",
                                 each if 
                                         Table.IsEmpty([Merge1])
                                      then
                                          if 
                                             Table.IsEmpty([Merge2]) 
                                          then 
                                             "No Matches" 
                                          else 
                                              Record.Field([Merge2]{0},"Result")
                                      else
                                          Record.Field([Merge2]{0},"Result"),
                                   type text)
in
    FinalTable

​

 

[billszysz]

let
    Lookup = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],
    DataTbl = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],
    #"Added Custom" = Table.AddColumn(DataTbl, "Returned Values", each try Lookup{[Column 1 = [Column 1]]}[Result] otherwise Lookup{[Column 2 = [Column 2]]}[Result])
in
    #"Added Custom"