Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lipesouza12
Frequent Visitor

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

KPICOMERCIALACTIVE
SALES KPI40109222683778
SERVICES KPI440022944251
RENEWS KPI2418121618
MONETIZE KPI4369329236
AMOUNT43404295658883

 

An table PERFORMANCE

SELLERGROUPKPI
SELLER 1COMERCIAL 
SELLER 2COMERCIAL 
SELLER 3ACTIVE 
SELLER 4ACTIVE 
SELLER 5COMERCIAL 
SELLER 6COMERCIAL 
1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @lipesouza12 ,

 

Please try the following code.

 

=
let 
  a = [GROUP]
in 
  Record.Field(GOALS{List.PositionOf(GOALS[KPI], "AMOUNT")}, a)

vkkfmsft_0-1654654568132.png

 

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.

View solution in original post

4 REPLIES 4
v-kkf-msft
Community Support
Community Support

Hi @lipesouza12 ,

 

Please try the following code.

 

=
let 
  a = [GROUP]
in 
  Record.Field(GOALS{List.PositionOf(GOALS[KPI], "AMOUNT")}, a)

vkkfmsft_0-1654654568132.png

 

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!

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Hi @watkinnc 

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors