Power Query Lookup to another table

[SP3007]

Hello, I need some help please!

I need to add a new column to my table. It needs to bring back a value by looking up a column against another table.

ie - column it needs to look upto is: CCA1

The other table with CCA1 is called - Helper_CCA1_Service and the column it needs to bring back is OrgStructServiceID

Im not very technical at all - and was given this code to use - but it just keeps bringing back an error:

= Table.AddColumn(#"5 - Added CCA1 Column", "OrgStructServiceID", each (let CCA1 = [CCA1] in Table.SelectRows(Helper_CCA1_Service, each [CCA1] = CCA1)){0}[OrgStructServID], type number)

Can anyone please help why it keeps coming back with error?

Thanks,

[v-xinruzhu-msft · Accepted solution]

Hi @SP3007 

You can change the code to the following.

= Table.AddColumn(#"5 - Added CCA1 Column", "OrgStructServiceID", each List.Min(Table.SelectRows(Helper_CCA1_Service,(x)=>x[CCA1]=[CCA1])[OrgStructServID]))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SP3007]

Thanks for this...its now coming back as null rather than error so one step closer!

[v-xinruzhu-msft]

Hi @SP3007 

Can you send a little bit of the sample data in the two tables, because if the data IDs in the two tables are the same, it should not return a null value

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SP3007]

Your solution worked! Thank you. My data was wrong.

Thank you

[j_ocean]

Use the GUI to do a merge. It walks you through it pretty well. Code by hand as a last resort unless you're comfortable self-describing as "a coder."

[SP3007]

Thanks for your response - as mentioned im not techy at all so can't see the difference with what you've suggested compared to what I originally pasted? 

[AlienSx]

@SP3007 I removed brackets after each and before {0}

[AlienSx]

Hello, @SP3007 those brackets... Why don't you Table.NestedJoin your table with helper? 

step
  = Table.AddColumn(
    #"5 - Added CCA1 Column",
    "OrgStructServiceID",
    each
      let
        CCA1 = [CCA1]
      in
        Table.SelectRows(
          Helper_CCA1_Service,
          each [CCA1] = CCA1
        ){0}[OrgStructServID],
    type number
  )