Stacking Data Columns

[FAW71]

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?

[Prince0011 · Accepted solution]

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

1. In Power BI Desktop, go to Transform Data to open Power Query.

2. Select the three columns:

   • 1st Reason

   • 2nd Reason

   • 3rd Reason

3. Right-click the selected columns and choose Unpivot Columns.

4. Power Query will create:

   • Attribute (e.g., 1st Reason, 2nd Reason, 3rd Reason)

   • Value (the actual reason selected)

5. Rename Value to Reason (and Attribute if desired).

6. Remove any blank/null rows.

7. 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

[FAW71]

When I unpivot I get "Error" in all columns. See below.

[FAW71]

I got it to work. I had to remove all the columns that had the value "Record" in them. Then the unpivot worked.

[rohit1991]

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.

[FAW71]

Is it possible to unpivot into a new table?
When I unpivot I get a longer list but all fields list "Error" in them.