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
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors