Displaying some columns as rows and some as columns

[Anonymous]

Hello everyone,

I have value like this :

How can I display this data in a table visual with a slicer in which I select then zone, and then get something like this :

Any help appreciated !

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can create a new table with below DAX. Replace 'Table' with your original table name. 

New Table = 
VAR _tableQuestion1 = SELECTCOLUMNS('Table',"Zone",'Table'[Zone],"Questions",'Table'[Question1],"Answers",'Table'[Answer Question1])
VAR _tableQuestion2 = SELECTCOLUMNS('Table',"Zone",'Table'[Zone],"Questions",'Table'[Question2],"Answers",'Table'[Answer Question2])
VAR _tableQuestion3 = SELECTCOLUMNS('Table',"Zone",'Table'[Zone],"Questions",'Table'[Question3],"Answers",'Table'[Answer Question3])
RETURN
UNION(_tableQuestion1,_tableQuestion2,_tableQuestion3)

Then use columns from this new table in the Zone slicer and the table visual. 

Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[rohit_singh]

This can be done using power query. You will first need to perform an unpivot transformation to reshape the data. Then get your data in the required format by using an index column and group by.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WijJU0lEKRMKOcByrA5Q2wi9tjF/aBL+0KX5pM/zS5vilLfBLW+KWjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, Q1 = _t, Q2 = _t, Q3 = _t, A1 = _t, A2 = _t, A3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}, {"A1", type text}, {"A2", type text}, {"A3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"(blank)"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Attribute", "Value"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
#"Added Conditional Column" = Table.AddColumn(#"Merged Columns", "Index", each if Text.Contains([Merged], "1") then 1 else if Text.Contains([Merged], "2") then 2 else if Text.Contains([Merged], "3") then 3 else 0),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Conditional Column", {{"Index", type text}}, "en-GB"),{"(blank)", "Index"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged.1"),
#"Grouped Rows" = Table.Group(#"Merged Columns1", {"Merged.1"}, {{"Count", each _, type table [#"(blank)"=nullable text, Attribute=text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Merged]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Values", {{"Merged.1", each Text.BeforeDelimiter(_, " -"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Merged.1", "Z"}})
in
#"Renamed Columns"

[Anonymous]

Could you redirect me to a tutorial or explain step by step ? I haven't ever pivotted a table.

And by the way, the source table cannot be altered itself as is it used in another visuals. Maybe we can simply make a copy of it and work from there ?

[amitchandak]

@Anonymous , First on all Unpivot all question and answer column in power query, Then use split by non digit and digit

then pivot the column that has non numeric values and values column from first unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Split Column By Digit to Non Digit & Non Digit to Digit: https://youtu.be/tY4Yk1crS9s