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.
Good morning,
Hopefully this is an easy solution to fix but I can't seem to wrap my head around it. I have a source file that gives me data in this format:
Column1 | Column2 | Column3 | Column4 | Column5 |
2019 | 2020 | 2020 | 2020 | |
12 | 01 | 02 | 03 | |
ID203 | 1000 | 1010 | 1015 | 1020 |
ID205 | 500 | 550 | 600 | 650 |
The first row has my year and the second row has my months. I want to dynamically unpivot the data so it could look like this:
Column1 | Column2 | Column3 | Column4 |
ID203 | 2019 | 12 | 1000 |
ID203 | 2020 | 01 | 1010 |
ID203 | 2020 | 02 | 1015 |
ID203 | 2020 | 03 | 1020 |
ID205 | 2019 | 12 | 500 |
ID205 | 2020 | 01 | 550 |
ID205 | 2020 | 02 | 600 |
ID205 | 2020 | 03 | 650 |
I drill adding a calculated column and using drilldown but it doesn't seem to give me the right results. Any help would be greatly appreciated!
Solved! Go to Solution.
Hi @hnguyen76 ,
1. Transpose Table
2. Replace Values with Header Names
3. Use First Row as Header
4. Mark the ID Columns and Unpivot Columns
Hi @mwegener ,
I just tried it with the sample example and it seems to be working. My dataset has one more column and row than the sample example.
I need to transform this:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 |
2019 | 2020 | 2020 | 2020 | ||
12 | 1 | 2 | 3 | ||
YTD | Period | Period | Period | ||
ID203 | TECH | 1000 | 1010 | 1015 | 1020 |
ID205 | TECH | 500 | 550 | 600 | 650 |
ID203 | SAM | 2000 | 2010 | 2015 | 2040 |
ID205 | SAM | 1000 | 1100 | 1200 | 1300
|
Into this:
Year | Month | Type | Key | Level1 | Value |
2019 | 12 | YTD | ID203 | TECH | 1000 |
2019 | 12 | YTD | ID205 | TECH | 500 |
2019 | 12 | YTD | ID203 | SAM | 2000 |
2019 | 12 | YTD | ID205 | SAM | 1000 |
2020 | 1 | Period | ID203 | TECH | 1010 |
2020 | 1 | Period | ID205 | TECH | 550 |
2020 | 1 | Period | ID203 | SAM | 2010 |
2020 | 1 | Period | ID205 | SAM | 1100 |
2020 | 2 | Period | ID203 | TECH | 1015 |
2020 | 2 | Period | ID205 | TECH | 600 |
2020 | 2 | Period | ID203 | TECH | 2015 |
2020 | 2 | Period | ID205 | TECH | 1200 |
2020 | 3 | Period | ID203 | SAM | 1020 |
2020 | 3 | Period | ID205 | SAM | 650 |
2020 | 3 | Period | ID203 | SAM | 2040 |
2020 | 3 | Period | ID205 | SAM | 1300 |
I can't seem to make it work as this is the result I'm getting from your steps:
Year | Month | Period | Attribute | Value |
ID203 | TECH | |||
ID205 | TECH | |||
ID203_1 | SAM | |||
ID205_2 | SAM | |||
2019 | 12 | YTD | ID203 | 1000 |
2019 | 12 | YTD | ID205 | 500 |
2019 | 12 | YTD | ID203_1 | 2000 |
2019 | 12 | YTD | ID205_2 | 1000 |
2020 | 1 | Period | ID203 | 1010 |
2020 | 1 | Period | ID205 | 550 |
2020 | 1 | Period | ID203_1 | 2010 |
2020 | 1 | Period | ID205_2 | 1100 |
2020 | 2 | Period | ID203 | 1015 |
2020 | 2 | Period | ID205 | 600 |
2020 | 2 | Period | ID203_1 | 2015 |
2020 | 2 | Period | ID205_2 | 1200 |
2020 | 3 | Period | ID203 | 1020 |
2020 | 3 | Period | ID205 | 650 |
2020 | 3 | Period | ID203_1 | 2040 |
2020 | 3 | Period | ID205_2 | 1300 |
Dang it. I had done this before. Totally forgot about it. Thanks @mwegener it worked! Sending you virtual hugs!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |