Forum Discussion
Reformatting messy data
Stop me if you've heard this one...
I've received an Excel spreadsheet from a user who wants to know if their data can be visualized in Power BI. The structure is set up for data entry rather than analysis, with 2 column dimensions and 2 row dimensions. I'm trying to figure out the easiest way to get it into dimension-fact format. Here's the structure:
| Name | Name | Name | Quest | Quest | Quest | Favorite Color | Favorite Color | Favorite Color | ||
| Topic | Subtopic | Yesterday | Today | Tomorrow | Yesterday | Today | Tomorrow | Yesterday | Today | Tomorrow |
| Topic A | Subtopic L | 4 | 2 | 4 | 4 | 5 | 2 | 2 | 2 | 4 |
| Topic A | Subtopic M | 2 | 1 | 4 | 2 | 3 | 3 | 1 | 1 | 2 |
| Topic A | Subtopic N | 1 | 2 | 5 | 2 | 2 | 2 | 3 | 3 | 1 |
| Topic A | Subtopic O | 1 | 3 | 5 | 5 | 4 | 4 | 2 | 1 | 5 |
| Topic B | Subtopic P | 4 | 1 | 4 | 5 | 2 | 4 | 2 | 2 | 2 |
| Topic B | Subtopic Q | 5 | 1 | 2 | 3 | 4 | 5 | 4 | 4 | 1 |
| Topic C | Subtopic R | 4 | 4 | 2 | 1 | 5 | 5 | 5 | 1 | 5 |
| Topic C | Subtopic S | 4 | 1 | 4 | 3 | 2 | 2 | 2 | 2 | 3 |
| Topic C | Subtopic T | 1 | 2 | 3 | 5 | 2 | 3 | 4 | 3 | 1 |
| Topic C | Subtopic U | 5 | 3 | 4 | 2 | 1 | 3 | 2 | 5 | 2 |
I've tried multi-step unpivot using Power Query/M, but that didn't get me to tidy data.
Before I redo everything manually, I figured I'd ask the experts. Any help/advice would be greatly appreciated, even if the answer is "You can't get there from here."
I think I get what you're trying to get to.
Have a look at the attached PBIX and let me know if you have any questions.
(apologies, it's a little rushed so I haven't renamed the steps or tidied up any of the code)
2 Replies
I think I get what you're trying to get to.
Have a look at the attached PBIX and let me know if you have any questions.
(apologies, it's a little rushed so I haven't renamed the steps or tidied up any of the code)