I have attached a file with some "data" from a hire purchase finance portal that my company uses.  The portal is used by many selling organisations across a few industries so the portal owners will not change the report for us.  The summary sheet shows a small subset of the data and the desired pivottables.

I have also described what the event is and what data items result from it.

You will see that the data presented to us is ... well awful!  After Mynda's recent blogpost on unpivoting badly layed out data I revisted the tutorials to see what I could achieve.  I thought I'd lay open my keen amateur attempts to see whether anyone fancied offering up some improvements.

I needed to clean the data a bit soon the sheet "attempt to clean" you will see the outcome - the query is basically me parsing through the table column by column cleaning as I go - not very elegant & inefficient. Thankfully the dataset is not massive so not the end of the line. Does anyone know if any of the table functions enable you to "replace values" across a whole table rather than a column at a time?

I then used "unpivot other columns" on this output in another query loading to sheet "attempt unpivot".  I am nearly there but I have now deduced what my final desired data structure looks like and as you can see that would involve splitting the contents of the "Outcome" column into two parts and this is where i am stumped!  Any inspiration would be gratefully received

Chris