topic Re: Power Query Lookup to another table in Power Query

Power Query Lookup to another table

SP3007 — Thu, 09 Nov 2023 10:58:10 GMT

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,

Re: Power Query Lookup to another table

AlienSx — Thu, 09 Nov 2023 11:44:37 GMT

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 )

Re: Power Query Lookup to another table

SP3007 — Thu, 09 Nov 2023 12:26:58 GMT

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?

Re: Power Query Lookup to another table

AlienSx — Thu, 09 Nov 2023 12:32:30 GMT

SP3007 I removed brackets after each and before {0}

Re: Power Query Lookup to another table

j_ocean — Thu, 09 Nov 2023 13:22:02 GMT

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."

Re: Power Query Lookup to another table

Anonymous — Tue, 14 Nov 2023 01:55:33 GMT

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.

Re: Power Query Lookup to another table

SP3007 — Tue, 14 Nov 2023 08:57:27 GMT

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

Re: Power Query Lookup to another table

Anonymous — Tue, 14 Nov 2023 09:17:16 GMT

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.

Re: Power Query Lookup to another table

SP3007 — Tue, 14 Nov 2023 11:31:27 GMT

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

Thank you