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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SAEDG
Frequent Visitor

Transpose or Upivot?

Hello everyone,

I am hoping someone can help me with transposing or unpivoting some columns.

I am basically trying to turn this:

 

SAEDG_1-1681765581693.png

 

Into this in Power Query Editor

 

SAEDG_0-1681766550690.png

 

I've tried unpivoting a few different ways, but no luck with attaching the reponse and the commentary to the same Question and ID.

 

Any help would be appreciated. 

Thanks!

1 ACCEPTED SOLUTION
DOLEARY85
Super User
Super User

Hi,

 

  1. First i'd duplicate the table,
  2. In one table delete all of the comment columns
  3. In the 2nd table delete all the question response columns and rename the comment columns to remove the word comment - they should match the column headings of the questions in table one.
  4. In each table select only the columns now labed with question # and select unpivot
  5. Select one of the tables and merge the other into it joining on the ID and qestion number
  6. Expand the column to bring in only the value field of the other table.

Hope this helps,

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

3 REPLIES 3
SAEDG
Frequent Visitor

This worked perfectly, thank you!

No problem, happy to help 🙂

DOLEARY85
Super User
Super User

Hi,

 

  1. First i'd duplicate the table,
  2. In one table delete all of the comment columns
  3. In the 2nd table delete all the question response columns and rename the comment columns to remove the word comment - they should match the column headings of the questions in table one.
  4. In each table select only the columns now labed with question # and select unpivot
  5. Select one of the tables and merge the other into it joining on the ID and qestion number
  6. Expand the column to bring in only the value field of the other table.

Hope this helps,

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.