transpose

[Anonymous]

Hello,

I want to know how I can transpose this data from columns to rows. I have lot of columns like that but just for example sharing an example,

Name	Plan	index	Project Name	Scheduled Date	L21	L213	L19	N19	L7	L8	L6	N6	L25 C1	L25 C2	N25 C1	N25 C2
A02046B	Existing	1	EU	10/28/2022	1	31	11	21	21		61	1
A02046B	Existing	2	EU	10/28/2022	2	32	12	22	22		62	2
A02046B	Existing	3	EU	10/28/2022	3	33	13	23	23		63	3
A02092B	New	1	COV	11/16/2022	1		11	21	21		61	1	131	141	301
A02092B	New	2	COV	11/16/2022	2		12	22	22		62	2	132	142	302
A02092B	New	3	COV	11/16/2022	3		13	23	23		63	3	133	143	303
B01062A	Existing	1	EU	11/30/2022	1		11	21	21		61	1	131		301	311
B01062A	Existing	2	EU	11/30/2022	2		12	22	22		62	2	132		302	312
B01062A	Existing	3	EU	11/30/2022	3		13	23	23		63	3	133		303	313
B02614A	Existing	1	AN	12/28/2022	1		11	21			61	1	131	141	301	311
B02614A	Existing	2	AN	12/28/2022	2		12	22			62	2	132	142	302	312
B02614A	Existing	3	AN	12/28/2022	3		13	23			63	3	133	143	303	313

output:

band is the data that is been transposed from L21 to N25 C2 and tech is the column that is telling which band as there are duplicates.

Name	Band	Scheduled Date	Project Name	Plan	Tech
A02046B	1	10/28/2022	EU	Existing	L21
A02046B	2	10/28/2022	EU	Existing	L21
A02046B	3	10/28/2022	EU	Existing	L21
A02046B	31	10/28/2022	EU	Existing	L213
A02046B	32	10/28/2022	EU	Existing	L213
A02046B	33	10/28/2022	EU	Existing	L213
A02046B	11	10/28/2022	EU	Existing	L19
A02046B	12	10/28/2022	EU	Existing	L19
A02046B	13	10/28/2022	EU	Existing	L19
A02046B	21	10/28/2022	EU	Existing	N19
A02046B	22	10/28/2022	EU	Existing	N19
A02046B	23	10/28/2022	EU	Existing	N19
A02046B	21	10/28/2022	EU	Existing	L7
A02046B	22	10/28/2022	EU	Existing	L7
A02046B	23	10/28/2022	EU	Existing	L7
A02046B	61	10/28/2022	EU	Existing	L6
A02046B	62	10/28/2022	EU	Existing	L6
A02046B	63	10/28/2022	EU	Existing	L6
A02046B	1	10/28/2022	EU	Existing	N6
A02046B	2	10/28/2022	EU	Existing	N6
A02046B	3	10/28/2022	EU	Existing	N6

[Mikelytics]

Hi @Anonymous.

What do you mean with derived from other columns? Do you work with Power BI Desktop? How did you et the data into Power BI Desktop?

Best regards

Michael

[Anonymous]

so in power querry those columns are not there, these are derived from combination of other columns that are there in the pbi

[Mikelytics]

Hi @Anonymous 

Sorry but I do not really understand.

Do you even use Power BI Desktop?

if yes, are the data you provided now in Power BI Desktop? 

If yes, how did you get the data in Power BI Desktop?

if no, do you have other data in Power BI Desktop and you want to add the data above?

if yes, where are these data stored?

A little bit more context would be helpful. 🙂

Best regards

Michael

[Mikelytics]

HI @Anonymous 

Please go in Power Query and select the first five columns of your dataset using STRG so that all of them are selected.

Then use the "unpivot other columns" feature.

You can find more information under:

Unpivot columns - Power Query | Microsoft Learn

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

[Anonymous]

Hello Mike,

these columns are derived from other columns so they are not in power querry editor. Can something be done in outside of power query editor?