Forum Discussion
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.
Your support is highly appreciated.
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 CreateYourTableCopy 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
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
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
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 CreateYourTableCopy 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
JimmyHi
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
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 resLogic is:
- Split source table to two columns, the first is header name column and the second is data column.
- List.Distinct(first column) get the unique header names.
- List.Split(second column, pagesize=the count of table rows/the count of header names).
