Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PijushRoy
Super User
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.

ParameterMeasure
Length3x35 SQMM
Length3x50 SQMM
Length3x70 SQMM
Length3x95 SQMM
Length3x120 SQMM
Length3x150 SQMM
Length3x185 SQMM
Length3x240 SQMM
Length3x300 SQMM
Length3x400 SQMM
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Rated Frequency50 Hz
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11
Voltage Grade6.35/11

 

I need to prepared my data following way.

LengthRated FrequencyVoltage Grade
3x35 SQMM50 Hz6.35/11
3x50 SQMM50 Hz6.35/11
3x70 SQMM50 Hz6.35/11
3x95 SQMM50 Hz6.35/11
3x120 SQMM50 Hz6.35/11
3x150 SQMM50 Hz6.35/11
3x185 SQMM50 Hz6.35/11
3x240 SQMM50 Hz6.35/11
3x300 SQMM50 Hz6.35/11
3x400 SQMM50 Hz6.35/11

 

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

Your support is highly appreciated.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
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

View solution in original post

6 REPLIES 6
shaowu459
Resolver II
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).
Jimmy801
Community Champion
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

Hi

It is working find, Thanks a lots.

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

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.

That would solve your problem.

 

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

Ilalready try and get the result as below snap

PijushRoy_0-1602236729781.png

but I require below format

LengthRated FrequencyVoltage Grade
3x35 SQMM50 Hz6.35/11
3x50 SQMM50 Hz6.35/11
3x70 SQMM50 Hz6.35/11
3x95 SQMM50 Hz6.35/11
3x120 SQMM50 Hz6.35/11
3x150 SQMM50 Hz6.35/11
3x185 SQMM50 Hz6.35/11
3x240 SQMM50 Hz6.35/11
3x300 SQMM50 Hz6.35/11
3x400 SQMM50 Hz6.35/11

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors