Convert values in a column to rows and show the data in Amt as values

I have a column period which has values JAN-24 to DEC-24. I want to have a column for each value in period column. There is a measure column Amt(highlighted in sheet 1) the values in this column needs to be populated in new columns formed from values present in period column. I'm attaching below a excel file. Sheet 1 represents current state of data and Sheet 2 is how I would like the data to be. Can anyone please suggest a solution? Thank you.

GL_Trans_Detail_Pivot.xlsx

Workflow

Datasets

Tips and Tricks

Common Use Cases

Transformation

Preparation

Accepted answers

Qiu

@CoG
Very smart one!

@srk0609
It did appear to me that in your desired output of the Excel file the months are in accending order.

but anyway, following suggestion from @CoG
This is how it looks like.

0918-srk0609-r1.yxmd

All comments

Qiu

@srk0609
We can do the CrossTab to bring the vertical to horizontal but with some small tricks since we want to keep the order of month.

0918-srk0609.yxzp

srk0609

what should I do get DEC-24 column first and JAN-24 in the last? Opposite of what it is currently in the Workflow that you provided? could you please let me know? Thank you

CoG

You would just need a way to reindex the NewPeriod field so that when sorted in increasing order the list ends up being reversed. There are smart ways to do this and then there's the simplest way for me share.

If you update @Qiu 's Formula Tool with the following:

ToString(999999-ToNumber(DateTimeFormat([DateTime_Out],"%Y%m")))+"_"+[period]

then you should achieve the desired result, which will work for the next several thousand years.

Happy solving!

Qiu

@CoG
Very smart one!

@srk0609
It did appear to me that in your desired output of the Excel file the months are in accending order.

but anyway, following suggestion from @CoG
This is how it looks like.

0918-srk0609-r1.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703