Data wrangling

[Haokun]

hi designer

I've a existing data set looks like below. some account has 2 years data, some not. 

Account	Year	Accounting Period	Sum_Base Amount
6182303000	2020	01/2020	75000
6182303000	2020	02/2020	65000
6182303000	2020	03/2020	55000
6182304000	2021	01/2021	45000
6182304000	2021	02/2021	35000
6182304000	2021	03/2021	25000
6182201000	2020	01/2020	30000
6182201000	2021	01/2021	40000
6182201000	2020	02/2020	50000
6182201000	2021	02/2021	40000
6182201000	2020	03/2020	50000
6182201000	2021	03/2021	50000

and i want to transform to the below format.can anyone give me some ideas? thanks a lot

Account	01/2020	01/2021	01 Var	02/2020	02/2021	02 Var	03/2020	03/2021	03 Var
6182303000	75000	0	-75000	65000	0	-65000	55000	0	-55000
6182304000	0	45000	45000	0	35000	35000	0	25000	25000
6182201000	3000	4000	1000	5000	4000	-1000	5000	5000	0

[Haokun]

hi i dont want to create formula tool and select tool to manually create the formulas and re-name the header in different year. becoz the formula need to be looking at the headers and i have to re-update the formula and select tool to change to the correct header sequence. The ideal solution would be get the variance before the cross tab.

[Qiu · Accepted solution]

@Haokun 
It turns out to be a lengthy one. 😁

[Haokun]

@Qiu 

hiii master Qiu,

thanks for your workflow. i didn't use your workflow but your concept did inspire me alot. in the end, i use the GL and period to append to a master list and join back to the raw data, the transactions are failed to join, i will set the amount to 0 and union back to the raw data. so my raw data has all the period. after that i will use a multi row tool to compute the variance. i am not sure if you are able to visualize the flow.