Forum Discussion
Power Query
Hi All, Can someone help me with Pivote, I know there is a sequence for transpose/pivote/unpivot to have a single row.
example:
| Data | |||||||
| Code | Month name | A | B | C | D | E | F |
| 100 | jan | M1 | 11 | 12 | 13 | 14 | 15 |
| 101 | jan | M2 | 21 | 22 | 23 | 24 | 25 |
| 102 | feb | M3 | 31 | 23 | 24 | 25 | 26 |
| 103 | feb | M1 | 41 | 24 | 25 | 26 | 27 |
| 104 | mar | M2 | 51 | 25 | 26 | 27 | 28 |
| 105 | mar | M1 | 61 | 26 | 27 | 28 | 29 |
| 106 | april | M3 | 71 | 27 | 28 | 29 | 30 |
| 107 | april | M5 | 81 | 28 | 29 | 30 | 31 |
| 108 | jan | M7 | 91 | 29 | 30 | 31 | 32 |
| 109 | feb | M9 | 101 | 30 | 31 | 32 | 33 |
| 110 | march | M11 | 111 | 31 | 32 | 33 | 34 |
Expected Output Like this below:
Hi Anonymous ,
According to your description, here's my solution.
1.Select columns from A to F, then click Unpivot Columns.
Result:
2. To sort the month name column, add a custom column:
if [Month name]="jan" then 1 else if [Month name]="feb" then 2 else if [Month name]="mar" then 3 else if [Month name]="april" then 4 else 5Sort Month name column by MonthNo column.
In a matrix, put Code in Rows, Month name and Attribute in Columns, get the result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
v-yanjiang-msftCommunity Support
Hi Anonymous ,
According to your description, here's my solution.
1.Select columns from A to F, then click Unpivot Columns.
Result:
2. To sort the month name column, add a custom column:
if [Month name]="jan" then 1 else if [Month name]="feb" then 2 else if [Month name]="mar" then 3 else if [Month name]="april" then 4 else 5Sort Month name column by MonthNo column.
In a matrix, put Code in Rows, Month name and Attribute in Columns, get the result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- some_bih
Community Champion
Hi Anonymous in Excel pivot, just drop columns Months names and Product into Columns area. I hope this help
Hi some_bih , Thanks for the reply, Only we use Power Query Editor,,......i am giving some example data in excel...
- some_bih
Hi Anonymous I do not understand your request. After creation of PQ transformation your output could be pivot table and then do as I described.
