Forum Discussion
How to convert row value to column
Hi,
Below is my data and i would like to have a single row for ID and company by pivoting other columns i.e if Business is finance, then its respecitve role,region and name.
| ID | Name | Business | Role | Region | Company |
| 100 | John | Finance | Manager | West | Walmart |
| 100 | Elliot | Sales | Analyst | East | Walmart |
| 100 | David | David | Manager | West | Walmart |
Expected output
| ID | Company | Finance_role | Finance_Region | Finance_Name | Sales_role | Sales_Region | Sales_Name | Photo_role | Photo_Region | Photo_Name |
| 100 | Walmart | Manager | West | John | Analyst | East | Elliot | Manager | West | David |
How can i acheive this in power bi?
Why would you ever want to do this? Goes against what Power Bi is about.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY09CsJAEIWvErZOEY+gJoKCIlpYLCkeOiQLkxnJrkJu705stNBpvsf7Ybx327r4Ple64oCBTKweMQjF+HZPyjSTuqBiaq3DHTK5tvRuUVVm7bS3aBMEcrX6HoKOxqwuFJMBPGBMH6OGOahFZzDFzKWAp7nc4MemxjPcMo+9Jv37pn0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]), #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}, {"Column5", Text.Trim, type text}, {"Column6", Text.Trim, type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID", "Company", "Business"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Column", each [Business] & "_" & [Attribute]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Business", "Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column]), "Column", "Value") in #"Pivoted Column"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
2 Replies
Why would you ever want to do this? Goes against what Power Bi is about.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY09CsJAEIWvErZOEY+gJoKCIlpYLCkeOiQLkxnJrkJu705stNBpvsf7Ybx327r4Ple64oCBTKweMQjF+HZPyjSTuqBiaq3DHTK5tvRuUVVm7bS3aBMEcrX6HoKOxqwuFJMBPGBMH6OGOahFZzDFzKWAp7nc4MemxjPcMo+9Jv37pn0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]), #"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}, {"Column2", Text.Trim, type text}, {"Column3", Text.Trim, type text}, {"Column4", Text.Trim, type text}, {"Column5", Text.Trim, type text}, {"Column6", Text.Trim, type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Trimmed Text", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ID", "Company", "Business"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Column", each [Business] & "_" & [Attribute]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Business", "Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column]), "Column", "Value") in #"Pivoted Column"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
i know but business wants this and asking to do in SQL which ends up creating lot of columns.. I wanted to know if we can do in power bi , so that we can pick only the columns that is asked.
