cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors