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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
New2PowerBI
Helper III
Helper III

Difficulty in unpivoting Table

I would like to unpivot a table; but I can't seem to easily unpivot after following a couple of tutorials.  I suspect it has to do with the data as it is, I think I need to clean it up some more. 

Here is what I have in Power BI right now and what I'd like it to look like:

Existing data snapshotExisting data snapshotDesired table structureDesired table structure

Basically, for every course an employee has a record of (the non-null values), I would expect a row, with the Course Name and corresponding information, such as Grace Period, Frequency and Priority. 

 

Any help will be greatly appreciated.  🙂

1 ACCEPTED SOLUTION

@New2PowerBI

1) Select Employee Name, Employee Number and Column 2 - Unpivot Other Columns

2) Select Column 2 - Pivot Column - Values Column select Value - Don't Aggregate

3) Rename Attribute Column (rearrange columns if you want)

Hope this helps! Smiley Happy

Query Editor - Unpivot then Pivot.gif

View solution in original post

6 REPLIES 6
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

Can you please provide some sample data?  Typing everything from your screenshot is not fun...

Sure!

 

Here is an example of what I brought in from Excel (a matrix).  The first two columns came in as one but I split them in Power BI, so there is the name and employee number now.

Employee NameEmployee NumberColumn2010.100 development…etc020.200 development…etc500.100 course xyz name020.200 development…etc
Anderson, Jesse123Frequency36121224
Anderson, Jesse123Priority1351
Anderson, Jesse123Grace Period3659060365

 

Here is where I want to get to so I can build my visualizations:

 

Course NameEmployee NameGrace PeriodFrequencyPriority
010.100 development…etcAnderson, Jesse365361
020.200 development…etcAnderson, Jesse90123
500.100 course xyz nameAnderson, Jesse60125
020.200 development…etcAnderson, Jesse365241

 

Something tells me the third column ( in original) is what it's not liking, Column2.  I feel like that is what is throwing off the unpivot option and need to break this down into more than one step to get it right. 

 

Thanks for your prompt response!  🙂

Got it:

 

  1. Duplicate your table
  2. In original table, remove column 2
  3. In orginal table, highlight the Employee Name and Number column, then right click and select "Unpivot Other Columns"
  4. In dupe table, remove the Employee Name, Number and Column 2 columns
  5. Demote headers
  6. Transpose table
  7. Rename columns (Course Name, Frequency, etc.)
  8. Create a Merge Query
    1. Select original table
    2. Select Merge Queries as New
    3. Select the "Attribute" column in original table
    4. Select "Course Name" column in dupe table
    5. Set Join Kind to Left Outer
  9. In Merged table, select Attribute column and remove dupes
  10. Remove the "Value" and "Attribute" column
  11. Expand the NewColumn column (with the nested tables)

Done!

 

Capture.PNG

@New2PowerBI

1) Select Employee Name, Employee Number and Column 2 - Unpivot Other Columns

2) Select Column 2 - Pivot Column - Values Column select Value - Don't Aggregate

3) Rename Attribute Column (rearrange columns if you want)

Hope this helps! Smiley Happy

Query Editor - Unpivot then Pivot.gif

**bleep** it Sean! You outdid me again! Plus a video... you're just showing off

Wow the word D A M N gets bleeped out?!

Wow!  Thank you!!!  I knew it had to be a two-step "something" but just couldn't figure it out.  I met with someone here at work yesterday for close to an hour and they couldn't figure it out.  😞

 

Amazing!  Thank you!!!  Loving the help and support in this community and looking forward to sharing with others!  🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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