Unpivot? & rename columns

[allan]

Hi, I've been unsuccessful at un-pivoting a table.  I've searched for some solutions already, and have downloaded some examples, but I'm no closer to a solution.

I think this is a combination of transpose, crosstab and dynamic rename but...I need some help!

My data looks like this:

id | year | population

a | 2020 | 10000

a | 2019 | 9999

b | 2020 | 20000

b | 2019 | 19999

Ideal output

id | population_2019 | population_2020

a | 9999 | 10000

b | 19999 | 20000

Best Regards,

Allan

[fmvizcaino]

Hi @allan ,

Attached is an example showing how to do it.

Take a look and let me know if this works for you.

Best,

Fernando Vizcaino

[allan]

Thank you for the quick response, can you screenshot the settings for the cross tab?

[cplewis90]

Here is a dynamic solution that ensures when you add more years you will be all set to go. It cleans up the data and headers from the original format to the final format.

[fmvizcaino · Accepted solution]

Sorry @allan ,

I've forgotten to share the workflow.

BEst,

Fernando Vizcaino

[allan]

Thank you both for your quick responses and both solutions work well. I accepted @fmvizcaino 's solution