[ms office - 2016] Exporting from SIMS, what is the best way to create the following tables?(Excel, PBI)

[Pollock]

Hi all,

 

I've been asked to collate exam data into the following tables:

 

[ATTACH=CONFIG]66186[/ATTACH]

 

[ATTACH=CONFIG]66187[/ATTACH]

 

The scale of the task looks considerable to my naïve mind, thus I'm certainly not looking for someone to provide a complete answer, but rather guidance on methods available within Excel or Power Query/BI that would make this possible.

 

I have attempted to use Power BI, but sadly this seems to require changes to the data within Power Query that break other visualisations (due to the sub-columns/dual headed columns).

 

In Excel, I can think, perhaps, of methods to tackle this, but I'm not as proficient as many here, and wanted to seek advice before embarking on an extremely long winded approach. #

 

Thanks in advance for your time and any help.