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
ngiam
Helper I
Helper I

Rearranging many headers in one column

Hi there,

I'm currently capturing data from Survey Monkey for a 360 survey

 

The data downloaded from Survey Monkey is as follow

RaterWork well togetherWork well together_1Work well together_2Work well together_3Offers HelpNote : same sequence after this...
 Rob SusanLindaCindyRob 
Cindy 54355 
Rob44211 

 

 

 

Question : How do I rearrange the headers to get the following?

 

RaterNameQuestionsRating
CindyRobWork well together5
CindySusanWork well together4
CindyLindaWork well together3
CindyCindyWork well together5
CindyRobOffers help5
RobRobWork well together4
RobSusanWork well together4

 

 

Thank you in advance!

1 ACCEPTED SOLUTION
ToddChitt
Super User
Super User

Does not look like you unpivoted correclty.

Where does that number come from in the original dataset? (Name = Cindy, "Work well together").

 

It may be that you need to UNPIVOT for each column in your dataset ([Work well together], [Offers Help]... etc.)

 

If this is SQL, consider doing the UNPIVOT with T-SQL as it can do all columns at once (I think).

View solution in original post

7 REPLIES 7
v-yangliu-msft
Community Support
Community Support

Hi  @ngiam ,

Here are the steps you can follow:

1. Power Query - Select all columns except [Rater] - Transform - Unpivot Columns.

vyangliumsft_0-1666857846373.png

Result

vyangliumsft_1-1666857846375.png

2. Create calculated table.

Table 2 =
FILTER('Table','Table'[Rater]<>BLANK())

3. Result:

vyangliumsft_2-1666857846376.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank you! this works!

ToddChitt
Super User
Super User

This dataset confuses me now, the more I look at it. Looks like you have TWO column headers. This may take a quite a bit of Power Query work.

Re-name "Works well together" to "Works well together\Rob" and so on for all columns. Then remove the second row by filtering it out. Then UNPIVOT each column in turn, starting with "Works well together\Rob". Result should look like this:

 

Rater            Attribute                                   Value

Cindy          Works well together\Rob          5

Cindy          Works well togehter_1\Susan    4

 

Next, do a Find and Replace of "_1" in Attribute, replace with empty string.

Finally, SPLIT the Attribute column on the "\"

 

ToddChitt
Super User
Super User

Does not look like you unpivoted correclty.

Where does that number come from in the original dataset? (Name = Cindy, "Work well together").

 

It may be that you need to UNPIVOT for each column in your dataset ([Work well together], [Offers Help]... etc.)

 

If this is SQL, consider doing the UNPIVOT with T-SQL as it can do all columns at once (I think).

Where does that number come from in the original dataset? (Name = Cindy, "Work well together")

The original was as per below : 

aterWork well togetherWork well together_1Work well together_2Work well together_3Offers HelpNote : same sequence after this...
 Rob SusanLindaCindyRob 
Cindy 54355 
Rob44211 

 

When you say UNPIVOT each column, do you mean selecting each separate column (as outlined above) and unpivot them?

ngiam
Helper I
Helper I

Thanks, Todd.

 

Unpivoting seems to show this. Is there anything that I should do differently?

AttributeValue
Work well togetherRob
Work well together_1Susan
Work well together_2Linda
Work well together5
Work well together_14
Work well together_23
  
  
ToddChitt
Super User
Super User

Looks like you would need to UNPIVOT the data. It looks like your data is currently PIVOTED, and showing summary columns. This is a Power Query operation. 

If your data is in the absolute granular state, then you can use a Matrix visual to display it either way.

 

After un-pivoting your data, it should be in a format like this:

Aspect           Name                  Rating

=====          ====                 =====

A                    Carol                   5

A                     Bob                    2

B                     Carol                   4

B                     Bob                     3

B                     Tim                     7

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.