Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I want to transform my data such that every time i update my excel sheet, bi should transform the data such that all the specified columns are below each other.
Raw Data:
Country | Region | Product | Sample Type | Q1 | Q2 | Q3 |
Inda | South | AD | AAA | 30 | 23 | 23 |
Inda | West | BO | DADD | 43 | 7 | 23 |
Inda | South | FG | SAA | 56 | 34 | 234 |
Inda | North | KS | AAA | 78 | 68 | 647 |
Inda | West | SI | SSD | 32 | 34 | 23 |
Inda | East | CF | FLK | 67 | 76 | 223 |
Inda | South | DJ | QEE | 12 | 34 | 5 |
What i want: Is it Possible??
Country | Region | Product | Sample Type | Q |
I | South | AD | AAA | 30 |
I | West | BO | DADD | 43 |
C | South | FG | SAA | 56 |
I | North | KS | AAA | 78 |
C | West | SI | SSD | 32 |
Y | East | CF | FLK | 67 |
D | South | DJ | QEE | 12 |
I | South | AD | AAA | 23 |
I | West | BO | DADD | 7 |
C | South | FG | SAA | 34 |
I | North | KS | AAA | 68 |
C | West | SI | SSD | 34 |
Y | East | CF | FLK | 76 |
D | South | DJ | QEE | 34 |
I | South | AD | AAA | 23 |
I | West | BO | DADD | 23 |
C | South | FG | SAA | 234 |
I | North | KS | AAA | 647 |
C | West | SI | SSD | 23 |
Y | East | CF | FLK | 223 |
D | South | DJ | QEE | 5 |
Solved! Go to Solution.
HI,
you could unpivot the columns in power query. If you are likely to have more quarters than just the 3 listed there - select all the columns except the quarters, right click and select unpivot other columns.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Okay, so all you have to do is go into power query select the ones you don't want in one column, right click the selection and select unpivot other columns
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Theres more than 50 columns.
Okay, so all you have to do is go into power query select the ones you don't want in one column, right click the selection and select unpivot other columns
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Thanks a lot, this solution worked perfectly as i wanted.
HI,
you could unpivot the columns in power query. If you are likely to have more quarters than just the 3 listed there - select all the columns except the quarters, right click and select unpivot other columns.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |