Forum Discussion
Helper Ibuilding 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......
Try not to laugh. Can someone help please?
10 Replies
CathyTthat separates the columns but the data is still not right....
- djurecic
Super User
What data are you expecting?
- CathyT
the data thats actually in Column58 and Column99 in table1
- djurecic
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?
- CathyT
yes it is,
- djurecic
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!
- CathyT
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.
- djurecic
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.
- CathyT
thanks for sticking with me and trying to help
