Require Help to Move Column Value to Column header in POWER QUERY

[PijushRoy]

Hi,

I have below mentioned data and I need to move the 1st column value to column header.

Parameter	Measure
Length	3x35 SQMM
Length	3x50 SQMM
Length	3x70 SQMM
Length	3x95 SQMM
Length	3x120 SQMM
Length	3x150 SQMM
Length	3x185 SQMM
Length	3x240 SQMM
Length	3x300 SQMM
Length	3x400 SQMM
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Rated Frequency	50 Hz
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11
Voltage Grade	6.35/11

I need to prepared my data following way.

Length	Rated Frequency	Voltage Grade
3x35 SQMM	50 Hz	6.35/11
3x50 SQMM	50 Hz	6.35/11
3x70 SQMM	50 Hz	6.35/11
3x95 SQMM	50 Hz	6.35/11
3x120 SQMM	50 Hz	6.35/11
3x150 SQMM	50 Hz	6.35/11
3x185 SQMM	50 Hz	6.35/11
3x240 SQMM	50 Hz	6.35/11
3x300 SQMM	50 Hz	6.35/11
3x400 SQMM	50 Hz	6.35/11

How I can management this, I already try to solve this by PIVOT option.

Your support is highly appreciated.

[shaowu459]

Hi @PijushRoy

Here is another way just for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8knNSy/JUNJRMq4wNlUIDvT1VYrVQRE2NcAqbI5d2BK7IYZG2JUb4jDd0AK7OUYm2NUbG2AXN0EWD0osSU1RcCtKLSxNzUuuBCoA2u5RNeLlwvJzShLTUxXcixJTUoEyZnrGpvqGhiNbLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parameter = _t, Measure = _t]),
    res = let a=Table.ToColumns(Source),
              b=List.Distinct(a{0})
          in 
              Table.FromColumns(List.Split(a{1},List.Count(a{0})/List.Count(b)),b)
in
    res

Logic is:

1. Split source table to two columns, the first is header name column and the second is data column.
2. List.Distinct(first column) get the unique header names.
3. List.Split(second column, pagesize=the count of table rows/the count of header names).

[Jimmy801 · Accepted solution]

Hello @PijushRoy 

you can apply some Table.Group by grouping by parameter and create a list from the measure column. In the second step a Table.FromColumns is used to us the data from the grouping-step to create your new table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8knNSy/JUNJRMq4wNlUIDvT1VYrVQRE2NcAqbI5d2BK7IYZG2JUb4jDd0AK7OUYm2NUbG2AXN0EWD0osSU1RcCtKLSxNzUuuBCoA2u5RNeLlwvJzShLTUxXcixJTUoEyZnrGpvqGhiNbLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parameter = _t, Measure = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Measure", type text}}),
    GroupYourData = Table.Group(ChangeType, {"Parameter"}, {{"Measures", each _[Measure], type table [Parameter=text, Measure=text]}}),
    CreateYourTable= Table.FromColumns
    (
        GroupYourData[Measures],
        GroupYourData[Parameter]
    )
in
    CreateYourTable

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PijushRoy]

Hi

It is working find, Thanks a lots.

Please suggest, How you implement this logic, so I can learn.

[Jimmy801]

Hello @PijushRoy 

then I would ask you to mark it as solution.

The best way to understand M-code to go to the microsoft power query homepage and check out the functions I used, and try to analyse what exactly does.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @PijushRoy ,

Go To Transform tab and select the first column and then do a Transpose.

That would solve your problem.

Appreciate your kudos!!! Please mark my post as solution if this helps you.

[PijushRoy]

Ilalready try and get the result as below snap

but I require below format

Length	Rated Frequency	Voltage Grade
3x35 SQMM	50 Hz	6.35/11
3x50 SQMM	50 Hz	6.35/11
3x70 SQMM	50 Hz	6.35/11
3x95 SQMM	50 Hz	6.35/11
3x120 SQMM	50 Hz	6.35/11
3x150 SQMM	50 Hz	6.35/11
3x185 SQMM	50 Hz	6.35/11
3x240 SQMM	50 Hz	6.35/11
3x300 SQMM	50 Hz	6.35/11
3x400 SQMM	50 Hz	6.35/11