Transforming complex data - grouped items converted to rows?

[ehrgeiz]

I've been working on this file for many hours and I have not been able to get the data into a proper table format.

The sample data is attached along with the sample of what I'm trying to achieve.

I've tried different techniques (namely using Chapter 15 of the M is For Data Monkey book) to transpose and unpivot complex data.

For one, the way the data is structured, there's dates at the top which need to be extrapolated and applied along the rows as well.

Please help! I've been at this all day and not sure how to approach the issue. If it was a single stacked column, I would be able to but it's almost like three columns of stacked data that needs to be broken out.

 

[ehrgeiz]

Is this something too complex to do with Power Query?

 

[Rudi]

Sorry for the lack of replies. I was holding back as I didn't want to others to assume the post had been answered already. I use PQ a lot, but I'm not expert in it. I just try to lend a hand where I can. This example is out of my league. I've experimented with it a few times and I cannot get it into the format you need. If you are willing to wait more, hopefully one of the gurus here will be able to supply some more relevant or helpful solution.

Good luck!

 

[Bill Szysz]

For PQ almost nothing is too complex ;-)))

 

[Rudi]

Very well done Bill! :clap2:

 

[horseyride]

Ugier, workable version

A much uglier version that Bill's, but it works and I did it as a project

 

[ehrgeiz]

Thanks a lot Bill and horseyride! I'm going to review these and if I have any questions, I hope you don't mind me pm'ing you. I really appreciate it.

Super helpful forum and posters!