Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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.
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.
Thanks for this...its now coming back as null rather than error so one step closer!
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.
Your solution worked! Thank you. My data was wrong.
Thank you
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."
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?
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
)