Power Query Pivot

[dslaugh]

I'm pretty new to Pivot tables as it is, much less one with so much information. I have two dates I'm working with. Shipped date and Received date. I want a chart showing the average number of days in "shipped" status by recipient. How do I get that information reflected in my pivot table?

[Bob Phillips]

You would probably need to add a calculated column for Received Date - Shipped Date and add that to the pivot as well as recipient, then send the value setting as average.

Did you really mean Power Query as well, because if you are using that you can add a custom column for the days in shipping, and you could even group by recipient with an aver4age aggregation.

[dslaugh]

Yes, it's coming from power query. I'll give it a gander and check back in. Thanks.

[Bob Phillips]

If you did it all in PQ, no need for a pivot, you could output the grouped results directly to an Excel worksheet table.

[Bob Phillips]

I just knocked up an example

Please Login or Register  to view this content.

It's that simple.

[dslaugh]

I got it to work. Thanks all.

[Bob Phillips]

Can you tell how you did it, just for completeness should anyone get this thread in a search.

[dslaugh]

I created a custom column in the PQ and subtracted the shipped date from the received date for the result in that column. Then, in the pivot, I placed that column in the "values" box, and changed the value field settings to Average. I then created my pivot chart off the table.

[Bob Phillips]

Thanks. Maybe try the group by option just for your info?