cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Super User

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

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.

1 ACCEPTED SOLUTION
Community Champion

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

6 REPLIES 6
Resolver II

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).
Community Champion

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

Super User

Hi

It is working find, Thanks a lots.

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

Community Champion

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
Not applicable

Hi @PijushRoy ,

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

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

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors