Best way to structure Excel survey data for leveraging in Power BI?

[jessewysong]

Hey folks,

I was hoping for advice on how to perhaps utilize excel survey data effectivly in Power BI. I have crated a cleaning log for our warehouse staff; they point their work tablets at QR codes posted in different sectors, and run through an excel survey to qiockly log their cleaning actions and any safety concerns. I included an image below. Any advice on how to set a survey up so the data is easy to use in Power BI? I guess I mostly need to count yes/no values that are generated in differetnt columns, and look at it all through a date lens. 

I appreciate the help. 

Best,

Jesse

[Sean · Accepted solution]

@jessewysong

If you collect the data as pictured you'll most likely have to Unpivot on Import

However because we have the Query Editor which makes it beyond easy to do this - design your Survey the way you think it will look best! (and easiest to fill out)

Look at my responses here

http://community.powerbi.com/t5/Desktop/Multiple-Stacked-Bar-Charts/m-p/111938#M47176

also here

http://community.powerbi.com/t5/Desktop/Creating-a-chart-using-the-same-legend-to-different-columns/...

Hope this helps!

Good Luck!

[jessewysong]

Hey thanks for the help Sean... It took a little trial and error to figure out which fields needed to be pivoted vs. unpivoted, and I also had to adjust my survey so the questions were shorter (I added longer hints so the person filling it out would still know what it meant). 

Some progress below.