Pivot Table

Copper Contributor

Might anyone have a suggestion on how to do the following?

 

Trying to show the daily counts of Job stages for the 6 days in the columns

I have added 4/1 data to the Rows and Values fields.  and these display properly in the Pivot table

How can I get all of the Stage names to show in the 1st column of the Pivot table

How do I get the remaining Date data to show correctly in the Pivot.  

Currently the other columns in the Pivot just repeat the data from 4/1

 

kelvinwbruce_0-1655777544275.png

 

Greatly appreciated

2 Replies

@kelvinwbruce You need to flatten the data first. With PowerQuery you can "unpivot" the data. The attached file contains an example where I imported a portion of the date in your screenshot. Unpivoting or flattening means you create a tabular data base with one column for each field. Thus, you end up with one column for the Job, one for the Company, one for the Date and one for the Stage. From that table you can create the pivot table as you want it.

 

The Query in the attached file was made with PQ in Excel for the Mac (still beta). It has a different look and feel and requires a slightly different approach when connecting to a local table. But the bulk of the process is the same.

 

If you are not familiar with PQ, the link below is a good place to start. Chapter 13 in particular (scroll down a bit).

https://exceloffthegrid.com/power-query-introduction/ 

Thanks Riny, I really appreciate your input. I will step through your solution tonight and see I can get a functional pivot. My concerned that my data was not laid out for Pivot use. I am using formulas to get aging data, so sort of functional. Will let you know how I make out