How to convert row value to column

[unicorn19]

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? 

[lbendlin · Accepted solution]

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.

[unicorn19]

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.