Use variables as column names in List Selects

[lipesouza12]

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

[v-kkf-msft · Accepted solution]

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.

[lipesouza12]

Worked perfectly!

[Anonymous]

So it sounds like you were trying to attach the column to each row of the Performance Table, so and if statement would work:

Table.AddColumn(PriorStep, "KPI", each if [Group] = "COMMERCIAL" then Goals[COMMERCIAL] else Goals[Active])

make sure you use UPPPERCASE anywhere I got the case wrong.

--Nate

[lipesouza12]

Hi @Anonymous 

Thanks for help. Unfortunately the IF statement isn't appropriate as GROUP types can be created posteriorly forcing a code change each.