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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors