Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OPS-MLTSD
Post Patron
Post Patron

turn a wide table into a long table in power BI

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:

 

ResponseIDAre you familiar with the Cubic Program?How would you rate it's ease of useIf you chose unsure for easy of use, please explainHow would you rate how easily it can be acquired?If you chose unsure for ease of acquiring, please explainAre you familiar with the Boltic program?How would you rate it's ease of useIf you chose unsure for easy of use, please explainHow 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 IDResponseIDProgramEase of UseEase of Use UnsureEasily AcquiredEasily Acquired Unsure
Cubic 11Cubic     
Boltic 11Bolitc    
Cubic 22Cubic     
Boltic 22Boltic    

 

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

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @OPS-MLTSD ,

 

Select these two colulmn at the same time, and then click "Unpivot Columns".

vstephenmsft_0-1675660555888.png

vstephenmsft_1-1675660846777.png

And then split "Attribute" by space.

vstephenmsft_2-1675660976597.png

vstephenmsft_3-1675661042052.png

vstephenmsft_4-1675661062897.png

Remove the unneeded columns and rename "Attribute.6" as "Program".

vstephenmsft_5-1675661070805.png

Add a custom column to get your "Uniq ID".

vstephenmsft_7-1675661337471.pngvstephenmsft_8-1675661342375.png

 

 

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

 

 

 

 

 

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @OPS-MLTSD ,

 

Select these two colulmn at the same time, and then click "Unpivot Columns".

vstephenmsft_0-1675660555888.png

vstephenmsft_1-1675660846777.png

And then split "Attribute" by space.

vstephenmsft_2-1675660976597.png

vstephenmsft_3-1675661042052.png

vstephenmsft_4-1675661062897.png

Remove the unneeded columns and rename "Attribute.6" as "Program".

vstephenmsft_5-1675661070805.png

Add a custom column to get your "Uniq ID".

vstephenmsft_7-1675661337471.pngvstephenmsft_8-1675661342375.png

 

 

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

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @OPS-MLTSD  - you need to try Unpivoting the results - How and why to Unpivot data with Power Query - YouTube 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors