Forum Discussion
Use variables as column names in List Selects
Hi Community,
Today I'm stuck on a simple list selection. I need to load the KPI from another table according to the group the salesperson belongs to. One possibility would be an IF statement but the groups may vary, in this case I would like to select the Goals Table Column from the group the seller belongs to. I tried declaring a variable and calling it as column but it didn't work.
let Group = [GROUP] in
Goals & Group{List.PositionOf(Goals[KPI], "AMOUNT")}
And Tried
let Group = [GROUP] in
Goals{List.PositionOf(Goals[KPI], "AMOUNT")}Group
But both return me Expression.SyntaxError: Expected token Eof. Another problem, is the dimension of the table that exceeds the thousands of rows
heres the table sample GOALS
| KPI | COMERCIAL | ACTIVE |
| SALES KPI | 4010922 | 2683778 |
| SERVICES KPI | 44002 | 2944251 |
| RENEWS KPI | 241812 | 1618 |
| MONETIZE KPI | 43693 | 29236 |
| AMOUNT | 4340429 | 5658883 |
An table PERFORMANCE
| SELLER | GROUP | KPI |
| SELLER 1 | COMERCIAL | |
| SELLER 2 | COMERCIAL | |
| SELLER 3 | ACTIVE | |
| SELLER 4 | ACTIVE | |
| SELLER 5 | COMERCIAL | |
| SELLER 6 | COMERCIAL |
Hi lipesouza12 ,
Please try the following code.
= let a = [GROUP] in Record.Field(GOALS{List.PositionOf(GOALS[KPI], "AMOUNT")}, a)If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
Thanks for help. Unfortunately the IF statement isn't appropriate as GROUP types can be created posteriorly forcing a code change each.
Hi lipesouza12 ,
Please try the following code.
= let a = [GROUP] in Record.Field(GOALS{List.PositionOf(GOALS[KPI], "AMOUNT")}, a)If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Worked perfectly!
