PowerQuery code not working when trying to get Vlookup result between two tables

Question

I am working in Powerbi and in powerquery I'm trying to do equivalent formula as Vlookup in Excel.

Now I have several tables but have focused on the first to try and get it to work.

Table OLRR and OLMPDirekta

The wanted column and result that I want get in OLRR Table OLMPDirekta

The code I wrote with help of GTP for the new custom column in OLRR

if [CostType] = "Absorption (DL)" then let matchingRow = Table.SelectRows(OLMPDirekta, each [Date] = [Date] and [CostCenter] = [CostCenter]), result = if Table.RowCount(matchingRow) > 0 then matchingRow{0}[DiffNyOLMPDirekta] else null in result else null

The code in custom column

The result I get

Result

As you can see I get 2,2,2,2,2,2,2,2,2,2,2 but would like it to be 2,1,1,1,1,1,1,1 depending on what the value for the corresponding costcenter and date (month) it is in OLMPDirekta

Any ideas on how to do this. I don't think merge is right way to go for me.. Because depending on CostType I need to do this in another table and so on.

Thanks for all help

Answer 1

Try

x = Table.AddColumn(#"Grouped Rows","MPResultat",(x)=>if x[CostType] = "Absorption (DL)" then try Table.SelectRows(OLMPDirekta, each x[Date] = [Date] and x[CostCenter] = [CostCenter]){0}[DiffNyOLMPDirekta] otherwise null  else null )
in x