Transpose columns with null values

[sagar_agarwal]

Hello,

I wish to transpose my data (as shown in "Current State") into both the Desired State options. I was able to do it using a combination of filter, unique, cross tab & select tool - but I believe it not an efficient way to do it.

[BenMoss]

Interested to see what others come up with in regards to this but I've attached the way I would do this (but again I don't think its as efficient as it can be, in fact it's just a bit different to your own).

Ben

[DataBlender]

Hi @sagar_agarwal

I think you will just need a transpose, filter and cross tab to achieve your two desired states (attached).

With more than one record you'd have to introduce a field which you can use to group by (also attached)

[MarqueeCrew · Accepted solution]

How about using a single summarize tool?

GroupBy = Key
Max(Name)
Max(Age)
Max(City)
Max(State)
Max(Country)

You can join this back to the rest of your data.

Cheers,
Mark

[sagar_agarwal]

This was an amazingly simple solution (I wonder why I couldn't think this through). This worked perfectly fine for me. Thanks :)

[MarqueeCrew]

Are you suggesting that I'm inherently simple? I look for the easy way out; that's for sure.