Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thank you!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |