Table transpose in Power Query

[Anonymous]

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

[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.

[edhans · Accepted solution]

Before you transpose, demote the headers.

1. Click the dropdown for Use First Row as Headers
2. Select Use Headers as first row

then transpose