Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Change referenced table based on cell value

Hi,   I've created the equivalent of an INDEX/MATCH to do a two-way lookup to find out which row and column to look up based on values of two other columns in my data set which looks like this: ...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    Hi Anonymous,

     

    Try each Record.Field(Expression.Evaluate("#""" & "Employees, 2022" &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column])).

    I guess, you know how/what to subsitute the "Employees, 2022" string with.

     

    Be careful though, I think I had some issues with using #shared in the PBI online context (although it was Ok with Dataflows) with scheduled refresh. test it before deployment.

     

    Kind regards,

    John 

     

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

Hi Anonymous,

 

Try each Record.Field(Expression.Evaluate("#""" & "Employees, 2022" &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column])).

I guess, you know how/what to subsitute the "Employees, 2022" string with.

 

Be careful though, I think I had some issues with using #shared in the PBI online context (although it was Ok with Dataflows) with scheduled refresh. test it before deployment.

 

Kind regards,

John 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    John, that's perfect!  I've got a column called 'Target Table' so just added that in as follows

    each Record.Field(Expression.Evaluate("#""" & [Target Table] &"""", #shared){[Users=[AUTHOR_NAME]]},[Target Column]))

    Thanks for your help.

     

    Rob