Forum Discussion
Creating a new table in Power BI combining columns
I need to create a new table combining six columns into two columns and keep the identifiers. The exisiting table is:
| question_id | respondent_id | Pos1 | Pos2 | Pos3 | Neg1 | Neg2 | Neg3 |
| 1 | 1 | x | |||||
| 2 | 1 | x | |||||
| 3 | 1 | x | |||||
| 1 | 2 | xx | |||||
| 2 | 2 | xx | |||||
| 3 | 2 | x |
The new table should look like this:
| question_id | respondent_id | Positive | Negative |
Positive should be a union of Pos1, Pos2, Pos3 and Negative Neg1, Neg2, Neg3
Any thoughts?
Hi Jannis68,
If I understand you correctly, you should be able to use the Unpivot Columns option in Query Editor to unpivot the Pos columns and Neg columns separately to get the expected result in your scenario.
For more details about how to Pivot and Unpivot with Power BI, you can refer to this article. :smileyhappy:
Regards
7 Replies
v-ljerr-msftMicrosoft Employee
Hi Jannis68,
If I understand you correctly, you should be able to use the Unpivot Columns option in Query Editor to unpivot the Pos columns and Neg columns separately to get the expected result in your scenario.
For more details about how to Pivot and Unpivot with Power BI, you can refer to this article. :smileyhappy:
Regards
I have no preference wheter using on import or when creating a DAX.
- Zubair_Muhammad
Community Champion
Using DAX you can create a calculated Table
Go to Modelling Tab>>>NEW TABLE and use this formula
Table = SUMMARIZE ( TableName, TableName[question_id], TableName[respondent_id], "Positive", SUM ( TableName[Pos1] ) + SUM ( TableName[Pos2] ) + SUM ( TableName[Pos3] ), "Negative", SUM ( TableName[Neg1] ) + SUM ( TableName[Neg2] ) + SUM ( TableName[Neg3] ) )
