Reformatting messy data

[Anonymous]

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."

[KNP · Accepted solution]

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)

[Anonymous]

Wow! It does just what I need. I'll need to study this a while to understand how you decomposed and recomposed the original table. Amazing.

Thank you so much!