Forum Discussion
Calculated Table with UNION and SELECTCOLUMNS
How do you create a calculated table with UNION(SELECTCOLUMNS()) to merge some columns and then add other columns as they are with SELECTCOLUMNS()?
I have the following columns:
- Calendar Date
- Business Group
- Total Variance
- Variance Level 1
- Variance Level 2
I want to transpose Total Variance, Variance Level 1, and Variance Level 2 into a "Category" and "Value" column. I have used UNION(SELECTCOLUMNS()) to achieve this. But I still want to keep Calendar Date and Business Group columns as they are in the new table. How can I add to this calculation to achieve that?
Played around until I found my answer - for anyone wondering, here is what I did:
UNION( SELECTCOLUMNS(Plant_Variances, "Category", "Total Variance", "Value", Plant_Variances[Total_Variance], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group]), SELECTCOLUMNS(Plant_Variances, "Category", "Variance Level 1", "Value", Plant_Variances[Variance_Level_1], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group), SELECTCOLUMNS(Plant_Variances, "Category", "Variance Level 2", "Value", Plant_Variances[Variance_Level_2], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group]))
2 Replies
Played around until I found my answer - for anyone wondering, here is what I did:
UNION( SELECTCOLUMNS(Plant_Variances, "Category", "Total Variance", "Value", Plant_Variances[Total_Variance], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group]), SELECTCOLUMNS(Plant_Variances, "Category", "Variance Level 1", "Value", Plant_Variances[Variance_Level_1], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group), SELECTCOLUMNS(Plant_Variances, "Category", "Variance Level 2", "Value", Plant_Variances[Variance_Level_2], "Calendar Date", Plant_Variances[Calendar_Date], "Business Group", Plant_Variances[Business_Group]))- VahidDM
Super User
HI Anonymous
Try this:
You can use the UNION function along with the SELECTCOLUMNS function to merge the columns you want to transpose into a "Category" and "Value" column and keep the other columns as they are in the new table. Here is an example of how you can do this:
- Use the SELECTCOLUMNS function to create a new table for each column you want to transpose (Total Variance, Variance Level 1, and Variance Level 2), specifying the "Category" and "Value" column names for each.
Total Variance = SELECTCOLUMNS(FILTER(OriginalTable, OriginalTable[Total Variance] <> BLANK()), "Category", "Total Variance", "Value", OriginalTable[Total Variance]) Variance Level 1 = SELECTCOLUMNS(FILTER(OriginalTable, OriginalTable[Variance Level 1] <> BLANK()), "Category", "Variance Level 1", "Value", OriginalTable[Variance Level 1]) Variance Level 2 = SELECTCOLUMNS(FILTER(OriginalTable, OriginalTable[Variance Level 2] <> BLANK()), "Category", "Variance Level 2", "Value", OriginalTable[Variance Level 2])- Use the UNION function to combine the three new tables into one table:
TransposedTable = UNION(Total Variance, Variance Level 1, Variance Level 2)- Select the columns you want to keep (Calendar Date and Business Group) from the original table, and add it to the transposed table using the JOIN function.
FinalTable = JOIN(TransposedTable,FILTER(OriginalTable, OriginalTable[Calendar Date] <> BLANK()), "Calendar Date")Note that in above code
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn | Twitter | Blog | YouTube
, I have used FILTER function to check if the columns are not empty before adding to the final table.
