- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 snapshot
Desired 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. 🙂
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you please provide some sample data? Typing everything from your screenshot is not fun...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 Name | Employee Number | Column2 | 010.100 development…etc | 020.200 development…etc | 500.100 course xyz name | 020.200 development…etc |
Anderson, Jesse | 123 | Frequency | 36 | 12 | 12 | 24 |
Anderson, Jesse | 123 | Priority | 1 | 3 | 5 | 1 |
Anderson, Jesse | 123 | Grace Period | 365 | 90 | 60 | 365 |
Here is where I want to get to so I can build my visualizations:
Course Name | Employee Name | Grace Period | Frequency | Priority |
010.100 development…etc | Anderson, Jesse | 365 | 36 | 1 |
020.200 development…etc | Anderson, Jesse | 90 | 12 | 3 |
500.100 course xyz name | Anderson, Jesse | 60 | 12 | 5 |
020.200 development…etc | Anderson, Jesse | 365 | 24 | 1 |
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! 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Got it:
- Duplicate your table
- In original table, remove column 2
- In orginal table, highlight the Employee Name and Number column, then right click and select "Unpivot Other Columns"
- In dupe table, remove the Employee Name, Number and Column 2 columns
- Demote headers
- Transpose table
- Rename columns (Course Name, Frequency, etc.)
- Create a Merge Query
- Select original table
- Select Merge Queries as New
- Select the "Attribute" column in original table
- Select "Course Name" column in dupe table
- Set Join Kind to Left Outer
- In Merged table, select Attribute column and remove dupes
- Remove the "Value" and "Attribute" column
- Expand the NewColumn column (with the nested tables)
Done!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Wow the word D A M N gets bleeped out?!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Subject | Author | Posted | |
---|---|---|---|
07-26-2024 12:47 AM | |||
03-26-2024 11:31 AM | |||
02-12-2024 10:42 AM | |||
05-21-2024 11:31 PM | |||
05-24-2024 05:19 AM |
User | Count |
---|---|
141 | |
112 | |
83 | |
63 | |
47 |