grouping unpivoted survey data

[rnehrboss]

Hi,

I can't figure out how to display grouped answer data for a survey.  We have the same survey given over a period of time. I'd like to create a visual (maybe a 100% stacked column with a legend with answers and a column for each month) that allows grouping by date.

Here is sample data:

ID	Attribute	Value
1	Date	4/1/2020
1	Question 1	Agree
1	Question 2	Strongly Agree
1	Question 3	Disagree
2	Date	4/12/2020
2	Question 1	Strongly Agree
2	Question 2	Disagree
2	Question 3	Agree
3	Date	4/12/2020
3	Question 1	Strongly Agree
3	Question 2	Disagree
3	Question 3	Agree
4	Date	5/1/2020
4	Question 1	Neutral
4	Question 2	Agree
4	Question 3	Disagree

Thanks for any help!

[HotChilli · Accepted solution]

I shifted the date to it's own column in Power Query. Here's some code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJLEkFUib6hvpGBkYGSrE6EPHA0tTiksz8PAUQxzG9KDUVU8oIyAkuKcrPS8+pVMChxhhkR2ZxIlzWCMVSI4StRui2YjHaCN16DKNRLEZoNMZlqzERthrjtdUYt60mCFtNkQPYBN1Sv9TSkqLEHExJIwzzcIRsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Attribute", type text}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.FromText([Value])),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "SurveyDate"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Attribute] <> "Date")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"SurveyDate", type date}})
in
    #"Changed Type1"

and then in powerbi you can get something like this

If the x-axis is set to 'categorical' in the format section, you won't get gaps.  Also wasn't sure whether UK or US dates are being used.

Anyway, that might give you some ideas.

You could also search the forum for 'Likert' for more suggestions.  Good luck.

[rnehrboss]

Great work.  Thanks.  don't know why I didn't think to not unpivot the date column.

Do you happen to know if there is an easy way to put n values (IE if March has 200, responses, April has 300, put 200 label in the bar or below the x axis values?

[HotChilli]

"Do you happen to know if there is an easy way to....."  - not off the top of my head.  You could turn on data labels in the Format section.  You could create a measure to count responses and put that in tooltip section.  Just some ideas..