Unpivot? & rename columns

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

Workflow

Preparation

Accepted answers

fmvizcaino

Sorry @allan ,

I've forgotten to share the workflow.

BEst,

Fernando Vizcaino

2020-02-17 Unpivot & rename columns.yxmd

All comments

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.

Data clean up (unpivot).yxmd

fmvizcaino

Sorry @allan ,

I've forgotten to share the workflow.

BEst,

Fernando Vizcaino

2020-02-17 Unpivot & rename columns.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703