Forum Discussion
Stacking Data Columns
I am working on an HR exit survery. I am using and MS Form to collect the answers and then a Power Automate flow to copy them to a SharePoint list. As part of the survey there are three question, select the 1st, 2nd, and 3rd reasons for leaving. Each one of these questions has the same possible answer selections.
I want to stack these three columns of data on top of each other so I can see the total number of times each answer was selected. For example, if "Family Concerns" was picked in the 1st and 3rd reasons that should show Family Concerns was used twice.
How do I go about this?
Yes, this is a common scenario. Since your 1st, 2nd, and 3rd Reason for Leaving columns all contain the same list of values, the best approach is to unpivot these columns in Power Query. This converts them into a single "Reason" column, making it easy to count how many times each reason was selected regardless of whether it was the 1st, 2nd, or 3rd choice.
Recommended approach: Unpivot the columns
In Power BI Desktop, go to Transform Data to open Power Query.
Select the three columns:
1st Reason
2nd Reason
3rd Reason
Right-click the selected columns and choose Unpivot Columns.
Power Query will create:
Attribute (e.g., 1st Reason, 2nd Reason, 3rd Reason)
Value (the actual reason selected)
Rename Value to Reason (and Attribute if desired).
Remove any blank/null rows.
Close & Apply.
Your data will change from:
Employee 1st Reason 2nd Reason 3rd Reason
A Family Concerns Salary Career Growth B Salary Family Concerns Family Concerns to:
Employee Reason Rank Reason
A 1st Reason Family Concerns A 2nd Reason Salary A 3rd Reason Career Growth B 1st Reason Salary B 2nd Reason Family Concerns B 3rd Reason Family Concerns Now you can simply create a bar chart with:
Axis: Reason
Values: Count of Reason
In this example, Family Concerns would correctly show a count of 3 because it was selected three times across all responses.
This approach is scalable, easy to maintain, and is the standard Power BI method for survey data where multiple columns represent the same type of response.
For more information:
Unpivot columns in Power Query: https://learn.microsoft.com/power-query/unpivot-column
Power Query documentation: https://learn.microsoft.com/power-query/
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
5 Replies
Hii FAW71
Use Power Query to unpivot the three reason columns. Select Reason 1, Reason 2, and Reason 3, then choose Transform > Unpivot Columns. Rename the resulting Value column to Leaving Reason, filter out blanks, and use it on the chart axis with Count of Leaving Reason as the value. This converts every selection into a separate row, so the same reason selected in different positions is counted each time.
Is it possible to unpivot into a new table?
When I unpivot I get a longer list but all fields list "Error" in them.
Yes, this is a common scenario. Since your 1st, 2nd, and 3rd Reason for Leaving columns all contain the same list of values, the best approach is to unpivot these columns in Power Query. This converts them into a single "Reason" column, making it easy to count how many times each reason was selected regardless of whether it was the 1st, 2nd, or 3rd choice.
Recommended approach: Unpivot the columns
In Power BI Desktop, go to Transform Data to open Power Query.
Select the three columns:
1st Reason
2nd Reason
3rd Reason
Right-click the selected columns and choose Unpivot Columns.
Power Query will create:
Attribute (e.g., 1st Reason, 2nd Reason, 3rd Reason)
Value (the actual reason selected)
Rename Value to Reason (and Attribute if desired).
Remove any blank/null rows.
Close & Apply.
Your data will change from:
Employee 1st Reason 2nd Reason 3rd Reason
A Family Concerns Salary Career Growth B Salary Family Concerns Family Concerns to:
Employee Reason Rank Reason
A 1st Reason Family Concerns A 2nd Reason Salary A 3rd Reason Career Growth B 1st Reason Salary B 2nd Reason Family Concerns B 3rd Reason Family Concerns Now you can simply create a bar chart with:
Axis: Reason
Values: Count of Reason
In this example, Family Concerns would correctly show a count of 3 because it was selected three times across all responses.
This approach is scalable, easy to maintain, and is the standard Power BI method for survey data where multiple columns represent the same type of response.
For more information:
Unpivot columns in Power Query: https://learn.microsoft.com/power-query/unpivot-column
Power Query documentation: https://learn.microsoft.com/power-query/
💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards, Prince Singh | Data Science & Microsoft Fabric Enthusiast
