Forum Discussion
Stack data in power bi
hello,
I am trying to stack data through data modeling. below is the scenerio. it wolud be greast help to me if anybody could suggest how to get the desired ouptut through Query/Dax. file attached https://1drv.ms/x/s!AqSgJWIdQo7TjDWaa1bYPYvHEBkq?e=h807Cx
Hi rohitjmd ,
Choose the Respid and category column in power query and unpivot other columns. Then rename the columns to get the excepted result.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZG5EcMgEEV7IVbA3ktouwxG/bdhLsvAyJ5hIx7/WHIOlJjdOBzhUQbKSJnvOY8cgMUFfDD4g2E2ru+f1wUN+sMQgSr6wlQlnhg0dHJaGB1al1dSVqperyVHV8Kug8DoeMNIU2uMYAKJi5dteUAZOfLC+J5HySytOnvmsh4XpbFDmlym7okcCP8zZqB4111bt97ditVd996u5fGYVOPwiuPvJ6/zDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Respid = _t, Category = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t, Q5 = _t, Q6 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Respid", Int64.Type}, {"Category", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}, {"Q5", Int64.Type}, {"Q6", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category", "Respid"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Question"}, {"Value", "Answer"}}) in #"Renamed Columns"
3 Replies
After you have imported the data into Power BI Desktop, click on "Edit queries" in the Home-ribbon.
Mark the columns Respid and Category:
Go to transform-ribbon, and click on the small arrow next to "Unpivot columns":
and select "unpivot other columns". Now rename columns appropriatly,
br,
S
Hi rohitjmd ,
Choose the Respid and category column in power query and unpivot other columns. Then rename the columns to get the excepted result.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZG5EcMgEEV7IVbA3ktouwxG/bdhLsvAyJ5hIx7/WHIOlJjdOBzhUQbKSJnvOY8cgMUFfDD4g2E2ru+f1wUN+sMQgSr6wlQlnhg0dHJaGB1al1dSVqperyVHV8Kug8DoeMNIU2uMYAKJi5dteUAZOfLC+J5HySytOnvmsh4XpbFDmlym7okcCP8zZqB4111bt97ditVd996u5fGYVOPwiuPvJ6/zDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Respid = _t, Category = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t, Q5 = _t, Q6 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Respid", Int64.Type}, {"Category", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}, {"Q5", Int64.Type}, {"Q6", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Category", "Respid"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Question"}, {"Value", "Answer"}}) in #"Renamed Columns"Thanks Guys
