Forum Discussion
Table transpose in Power Query
Hello,
I have the following table in Power Query and i am trying to obtain the transpose using Power Query.
| Start date | Stock | Returns | Defective | Category |
| 01/05/2021 | 6 | 9 | 17 | Late |
| 27/05/2022 | 2 | 5 | 10 | On time |
| 07/08/2022 | 5 | 6 | 15 | On time |
| 14/07/2022 | 5 | 6 | 15 | Undefined |
| 08/08/2022 | 3 | 4 | 8 | No action |
| 12/08/2022 | 2 | 2 | 10 | Late |
| 17/08/2022 | 7 | 8 | 22 | On time |
| 20/08/2022 | 3 | 3 | 13 | No action |
| 22/08/2022 | 6 | 3 | 18 | Late |
| 29/08/2022 | 3 | 3 | 14 | On time |
| 18/09/2022 | 3 | 3 | 14 | Undefined |
| 07/09/2022 | 2 | 7 | 17 | No action |
| 16/09/2022 | 3 | 7 | 22 | On time |
| 20/10/2022 | 9 | 3 | 16 | On time |
| 23/11/2022 | 7 | 5 | 20 | Late |
| 11/12/2022 | 9 | 8 | 18 | No action |
| 04/01/2023 | 6 | 7 | 16 | On time |
| 17/02/2023 | 7 | 14 | 34 | On time |
| 11/12/2022 | 9 | 8 | 18 | No action |
I tried the Transpose option in Power Query but the first column as shown below with the headers is missing.
Expected results:
| Start date | 01/05/2021 | 27/05/2022 | 07/08/2022 | 14/07/2022 | 08/08/2022 | 12/08/2022 | 17/08/2022 | 20/08/2022 | 22/08/2022 | 29/08/2022 | 18/09/2022 | 07/09/2022 | 16/09/2022 | 20/10/2022 | 23/11/2022 | 11/12/2022 | 04/01/2023 | 17/02/2023 | 11/12/2022 |
| Stock | 6 | 2 | 5 | 5 | 3 | 2 | 7 | 3 | 6 | 3 | 3 | 2 | 3 | 9 | 7 | 9 | 6 | 7 | 9 |
| Returns | 9 | 5 | 6 | 6 | 4 | 2 | 8 | 3 | 3 | 3 | 3 | 7 | 7 | 3 | 5 | 8 | 7 | 14 | 8 |
| Defective | 17 | 10 | 15 | 15 | 8 | 10 | 22 | 13 | 18 | 14 | 14 | 17 | 22 | 16 | 20 | 18 | 16 | 34 | 18 |
| Category | Late | On time | On time | Undefined | No action | Late | On time | No action | Late | On time | Undefined | No action | On time | On time | Late | No action | On time | On time | No action |
Before you transpose, demote the headers.
- Click the dropdown for Use First Row as Headers
- Select Use Headers as first row
then transpose
3 Replies
- edhans
Community Champion
Before you transpose, demote the headers.
- Click the dropdown for Use First Row as Headers
- Select Use Headers as first row
then transpose
- edhans
I agree with Greg_Deckler , but hard to know based on your post what you are doing. This could be an interim transformation for a merge or something, or it could be the end result needed for an Excel table, which would be fine.
But if this is the end result for a Power BI model, your original table is more along the lines of what Power BI works well with. - Greg_Deckler
Anonymous Also, just to add to what edhans said. I am fairly certain that you might want to reconsider transposing that table to begin with as the original format it is in is far more compatible with Power BI. In fact, I think it would be better if you unpivoted your Stock, Returns and Defective columns. I may be wrong, but if I had that data and wanted it to be easy to work with in Power BI, that's what I would probably do.
