Transforming data with all timeframe in a column

[JS]

Hello all. 

I have a raw data below and require certain transofmation and any advise/solution will be helpful. 

I have a column name Quartely Breakdown and it contains information as shown below. 

ID	Quarterly Breakdown
1	2018Q1: 5000
2	2018Q2: 2500; 2018Q3: 2400
3	2018Q1: 4000; 2018Q2: 4000; 2018Q3: 4000; 2018Q4: 5000

I wish to obtain a desired results below. 

ID	Period	Amount
1	2018 Q1	5000
2	2018 Q2	2500
2	2018 Q3	2400
3	2018 Q1	4000
3	2018 Q2	4000
3	2019 Q3	4000
3	2019 Q4	5000

I have tried unpivoting and pivoting the columns but still could not get the desired results. Thanks in Advance! 

JS

[v-jianhe-msft · Accepted solution]

Hi,

To achieve it, you could first split the column based on the delimiter “semicolon”, noted in “Advanced options”, select “rows”.

By doing this, the column will become

Then split the column again. This time only need to set delimiter as “Colon”:

And the final result is like:

BR,

Henry

[JS]

Thank you!!!