Power Query doesn't understand Excel formulas. You need to solve it using M functions.
I created a small example with two tables:
Book1 |
---|
|
---|
| A | B | C | D | E |
---|
1 | LookupTable | | | DataTable | |
---|
2 | Lookup Value | Result | | Lookup Letter | Condition |
---|
3 | A | 1 | | F | Whatever |
---|
4 | B | 2 | | A | Recruited |
---|
5 | C | 3 | | F | Whatever |
---|
6 | D | 4 | | D | Whatever |
---|
7 | E | 5 | | B | Recruited |
---|
8 | F | 6 | | I | Whatever |
---|
9 | G | 7 | | I | Whatever |
---|
10 | H | 8 | | J | Whatever |
---|
11 | I | 9 | | B | Recruited |
---|
12 | J | 10 | | J | Whatever |
---|
|
---|
The tables are called LookupTable and DataTable and here's a Query that adds the matching value from the LookupTable[Result] if there's "Recruited" on the DataTable[Condition]:
Power Query:
let
LookupTable= Table.Buffer(Excel.CurrentWorkbook(){[Name= "LookupTable"]}[Content]),
Source = Excel.CurrentWorkbook(){[Name= "DataTable"]}[Content],
ResultColumn = Table.AddColumn(
Source, "Result",
each if [Condition]="Recruited"
then LookupTable[Result]
{ List.PositionOf(LookupTable[Lookup Value], [Lookup Letter]) }
else null)
in
ResultColumn
The LookupTable -step at the beginning of my code is just the LookupTable. I wanted to buffer it in case it's actually a much bigger one than the one in my Example.
The lookup logic of the ResultColumn -step is a bit harder to understand so I'll try to explain it:
LookupTable[Result] is the list we want to return the result value from. The List.PositionOf -function returns the index number from the LookupTable[Lookup Value] -list that matches the contents of the DataTable[Lookup Letter] -field of that row. So basically it's the PowerQuery equivalent of Excel's INDEX/MATCH where the List.PositionOf is doing the MATCH part.