cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

grouping unpivoted survey data

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:

IDAttributeValue
1Date4/1/2020
1Question 1Agree
1Question 2Strongly Agree
1Question 3Disagree
2Date4/12/2020
2Question 1Strongly Agree
2Question 2Disagree
2Question 3Agree
3Date4/12/2020
3Question 1Strongly Agree
3Question 2Disagree
3Question 3Agree
4Date5/1/2020
4Question 1Neutral
4Question 2Agree
4Question 3Disagree

 

Thanks for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: grouping unpivoted survey data

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

Capturelikert.PNG

 

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.

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: grouping unpivoted survey data

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

Capturelikert.PNG

 

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.

View solution in original post

Highlighted
Regular Visitor

Re: grouping unpivoted survey data

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?

 
Highlighted
Super User I
Super User I

Re: grouping unpivoted survey data

"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..

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors