Forum Discussion
Combine columns by stacking on new table
Hello,
Is it possible to stack multiple columns from one table into a single column on a new table and then add a new column to filter off of? I'm sorry that is not clear here is an example of what I'm trying to do:
Take this
| Satisfied with Joe | Satisfied with Jane | Satisfied with Jerry |
| 3 | 3 | 1 |
| 4 | 3 | 2 |
and turn it into this
| Satisfied | Name |
| 3 | Joe |
| 4 | Joe |
| 3 | Jane |
| 3 | Jane |
| 1 | Jerry |
| 2 | Jerry |
Thanks
Select your columns and then Unpivot Columns (under Transform tab).
Should be left with this
And you can split the columns to get the names!
2 Replies
- Syk
Resident Rockstar
Select your columns and then Unpivot Columns (under Transform tab).
Should be left with this
And you can split the columns to get the names! - DataNinja777
Super User
Hi NBADM ,
You can achive this using Unpivot Columns.
To achieve this transformation in Power BI, start by loading your data into the Power Query Editor. Once in the editor, select the columns "Satisfied with Joe," "Satisfied with Jane," and "Satisfied with Jerry." Then, use the "Unpivot Columns" option, which can be accessed by right-clicking on the selected columns. This will transform the selected columns into rows, creating a table with two columns: one containing the original column headers and the other containing the corresponding values.
After unpivoting, rename the first column (which holds the original headers) to "Name" and the second column to "Satisfied." To clean up the "Name" column so it only contains the names (e.g., "Joe," "Jane," "Jerry"), use the "Transform" tab, selecting "Extract" and then "Text After Delimiter." Specify "with " as the delimiter to remove the "Satisfied with" prefix from each value.
Finally, close and apply the changes to load the transformed table back into Power BI. The resulting table will have two columns: "Satisfied" and "Name." It will now be in the desired format, making it easy to filter or use in visuals for analysis.
Best regards,
