Transposing then unpivot/crosstab data set?

[dshi7]

Okay so my base data looks like this:

The event #xx count can potentially go up to say 50

When I transpose, I can get the output half way to what I need it to look like:

Just no idea how to get to this final output:

Appreciate any help!

[JamesCharnley]

Hi @dshi7 

If you're always going to have an input and a score for every event, I think you should be able to filter out rows from your transposed data with contains([Name2], 'input') and then join your true and false outputs on record position and get rid of the duplicate columns.

Let me know if that works, maybe I can mock it up if I have time.

James

[DataNath · Accepted solution]

Can you try this and see how you get on, @dshi7? I had to mock up something smaller as you only provided screenshots rather than actual data to use. The only thing you'll need to change is perhaps the fields you want to exclude where you're currently using [Dummy] as the filter removes that. The rest ought to be dynamic but let me know if any issues:

[Emmanuel_G]

@dshi7 

Find attached the way of doing that;

Let me know if there is any issue and do not hesitate to mark this answer as solution if it helped.