Table transform

Hi everone,
I have a table 1 like this:

ID_Salers Year 1 Year2 Customers
1 552246 3574957 MarryQ
2 2479119 2479119 HarryM
3 475350 543258 TomH
4 936039 48738 MarryQ
5 797211 797211 HarryM
6 714555 952740 TomH
7 52020 2473843 MarryQ
8 23493 93972 HarryM
9 12865 102917 TomH
10 28038 8274384 MarryQ
11 10084 10084 TomH
12 5861 2323 MarryQ
13 23967 63912 HarryM
14 32939 43918 TomH
15 6318 7220 HarryM

And I would like to transform the above table to something like this:

ID_Salers Year MarryQ HarryM TomH
1 Year 1 552246
1 Year 2 3574957
4 Year 1 936039
4 Year 2 48738
7 Year 1 52020
7 Year 2 2473843
10 Year 1 28038
10 Year 2 8274384
12 Year 1 5861
12 Year 2 2323

Could you please give me some tips how to perform this task?
Thank you so much for your help
Best
Hanh

Hi @HanhDo,

i hope i get you correctly. My suggestion is

Step 1: Unpivot your table keeping Customers and ID_Saliers in Retained columns and Year 1 and Year 2 as Value Columns.
Step 2: Rename Column “ColumnNames” into “Year”
Step 3: Pivot the data table by setting ID_Salers and Year as group columns, Customers as Pivot column and ColumnValues as Aggregation column with aggregation mode “first”.

BR

4 Likes

hi @morpheus, you’re awesome, it works perfectly :smiley:
Thank you so much for your help
Best
Hanh

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.