Using Columns as Pivot Rows in Excel 2013

[MakingCharts]

https://www.dropbox.com/s/gbrvlaclbyznku1/sanitized_export.xlsx?dl=0

Was hoping someone could help me figure out how to use Power Query to use row values as columns.

My raw data is in 'Data Export', I use 'Root Cause Stack' to get rid of some columns and modify the format of the closed date column. Output of my query is in 'Root Cause Stack' worksheet.

I'd like to use the values of the root cause field as columns, so I can produce a stacked bar chart that shows root causes per day.

I put an example of how I want the data to look (just using a pivot table off of the 'Root Cause Stack' output) in the 'What I want' worksheet.

If anyone could help me figure out how to produce this view with a Power Query (rather than having a second step where I create a pivot table) I would appreciate it.

 

[scottsen]

If you have Power Pivot available, this would be entirely easier there...

 

[MakingCharts]

If you have Power Pivot available, this would be entirely easier there...

I have enabled the PowerPivot addin. I would like my report data grouped by day, but the raw exported data is in 'mm/dd/yyyy hh/mm/ss' format. Should I not be using Power Query to transform the data into 'mm/dd/yyyy' format? Is there an equivalent feature in PowerPivot? I see where I can change the format of the closeddate column, but this only changes the display, it doesn't actually remove the time.

Can you provide more detail on what you are suggesting?