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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PijushRoy
Community Champion
Community Champion

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors