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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CathyT
Frequent Visitor

building a table using a measure as a column name

I have a table (table1) imported from an Excel spreadsheet with over 100 columns.  The number of columns increases each week.

I created a copy of this table (table2) and used the pivot to search the columns to find a specific column that appears somewhere in the middle and also to find the last column with data.  This week its Column58 (measure col_dept) and Column99 (measure col_data).  Next week it may be Column59 and Column102, theres no telling.
I want to build another table (table3) that contains only those 2 columns from table1.  I'm very new to this language so I'm sure I'm barking up the wrong tree here, but this is what I tried. 

table3 =  SELECTCOLUMNS('table1',"dept,vacancies",[col_dept]&[col_data])

My result is the same number of rows as in table1 but......

CathyT_0-1669753290255.png

Try not to laugh.  Can someone help please?



10 REPLIES 10
djurecicK2
Super User
Super User

Ok, sorry for misunderstanding. I don't believe that is possible with the Selectcolumns function- it will not take a variable (or measure) as a column name.

thanks for sticking with me and trying to help

djurecicK2
Super User
Super User

Ok, sorry, my mistake. Please try this:

 

table3 = SELECTCOLUMNS('table1',"dept",[Column58], "vacancies",[Column99])

 

Please consider accepting as solution if this answers the question- thanks!

 

I must not have explained my issue very well.  The col_dept and col_data are measures created by searching table1 for a value.  The search returns which column that value is found in this week.  It will change every week so I don't want to hard code it into PowerBI.  I want the entire column that each of those measures was found in.
The value of measure col_dept is "Column58" this week.  The value of measure col_data is "Column99" this week.  So I want to plug the values "Column58" and "Column99" into the SelectColumns function.

djurecicK2
Super User
Super User

Ok, it seems like that excel screenshot is not what is in table1 in Power BI.

 

Can you use the data view in Power BI desktop to look at table1 there and post a screenshot?

djurecicK2_0-1669755821408.png

 

yes it is,

CathyT_0-1669756076812.png

 

djurecicK2
Super User
Super User

What data are you expecting?

the data thats actually in Column58 and Column99 in table1

CathyT_0-1669755640790.png

 

djurecicK2
Super User
Super User

Hi @CathyT ,

 You are close! The "&" combines the columns.

Try this

table3 =  SELECTCOLUMNS('table1',"dept",[col_dept], "vacancies",[col_data])

 

Please consider accepting as solution if this answers the question- thanks!

 

that separates the columns but the data is still not right....

CathyT_0-1669754997289.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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