Forum Discussion
Helper IIIPull data from unrelated table like VLOOKUP
I think it may be the blank control nos in the TRANS table which are causing problems. Try
PO # = var controlNumber = TRANS[control no] return IF( NOT ISBLANK(controlNumber), SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, OPENHEAD, OPENHEAD[ID]), TREATAS( { controlNumber}, OPENHEAD[control no]) ), "@val", OPENHEAD[PO #] ) )
Helper IIIthanks for the suggestion but this leads to the same error.
Here are screenshots if that might help.
This is the OPENHEAD table. Each CONTROL_NO is unique and has a CUST_PO column (sometimes they are blank).
This is the TRANS table. I need to insert a column that pulls the CUST_PO info. The common link is that both tables have CONTROL_NO.
Hope that helps to clarify.
Thanks a lot!
Super UserI think it may be the blank control nos in the TRANS table which are causing problems. Try
PO # =
var controlNumber = TRANS[control no]
return IF( NOT ISBLANK(controlNumber),
SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, OPENHEAD, OPENHEAD[ID]),
TREATAS( { controlNumber}, OPENHEAD[control no]) ),
"@val", OPENHEAD[PO #]
) )- gaiusgw
Still getting the same error. Im not sure that blanks are the issue because I cannot get passed the unexpected token.
- johnt75
Ah, the code I posted was DAX. You need to add it as a new calculated column, not in Power Query.
- gaiusgw
Gotcha, thanks for pointing me in the right direction. I gave it a go but still getting errors.
See the error at the bottom of this shot. Looks like it does not like that there are multiple lines with the same CONTROL_NO in the TRANS table.