Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a survey result from excel that I imported into power query, the excel table is currently in a wide format, for the purposes of my visualizations and analysis, I need to turn the wide table into a long table. This is what the table looks like now:
ResponseID | Are you familiar with the Cubic Program? | How would you rate it's ease of use | If you chose unsure for easy of use, please explain | How would you rate how easily it can be acquired? | If you chose unsure for ease of acquiring, please explain | Are you familiar with the Boltic program? | How would you rate it's ease of use | If you chose unsure for easy of use, please explain | How would you rate how easily it can be acquired? | If you chose unsure for ease of acquiring, please explain |
1 | ||||||||||
2 |
and this is what I would like the table to look like:
Uniq ID | ResponseID | Program | Ease of Use | Ease of Use Unsure | Easily Acquired | Easily Acquired Unsure |
Cubic 1 | 1 | Cubic | ||||
Boltic 1 | 1 | Bolitc | ||||
Cubic 2 | 2 | Cubic | ||||
Boltic 2 | 2 | Boltic |
If someone could please help me with this process, and let me know how I can turn the first wide table into a long table, I would really appreicate that.
thank you
Solved! Go to Solution.
Hi @OPS-MLTSD ,
Select these two colulmn at the same time, and then click "Unpivot Columns".
And then split "Attribute" by space.
Remove the unneeded columns and rename "Attribute.6" as "Program".
Add a custom column to get your "Uniq ID".
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @OPS-MLTSD ,
Select these two colulmn at the same time, and then click "Unpivot Columns".
And then split "Attribute" by space.
Remove the unneeded columns and rename "Attribute.6" as "Program".
Add a custom column to get your "Uniq ID".
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@v-stephen-msft
thank you! this is what I needed, now if I import some more survey results into power BI, would it change the entire table and do I have to redo everything? I have not changed anything about the excel sheet containing the survey results, ther are only more responses now
Hi @OPS-MLTSD - you need to try Unpivoting the results - How and why to Unpivot data with Power Query - YouTube