Stack data in power bi

[rohitjmd]

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

[v-frfei-msft · Accepted solution]

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"

[rohitjmd]

Thanks Guys

[sturlaws]

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