How to organize data to get correct fields in Pivot Table

Question

I have the data attached and have data for 100 more clinics and would like to calculate the percent change month to month of each clinic by service. I try to create a pivot table from the data I have but it is not grouping the months together. Any suggestions?

Answer 1

Hi Carlennis,

The issue here is that the data is being input into the PIVOT with each month as a separate field. To resolve this, you need to get the Dates (aka Months) into a single field in the original data table.

Follow these steps:

1. Remove the blank lines in your table. (I created this data based on your snapshot using random numbers).
   
2. The we need to UNPIVOT the all the values columns into only two DATE and VALUE columns. We can use Power Query in Excel to do this. Go to Data > Get Data > From Other Sources > From Table
3. In the Power Query editor, select the Clinic Name and Services fields (i.e. hold shift and click on each column header, A & B in the snapshot), then go to Transform > Unpivot Columns > Unpivot Other Columns
4. The Unpivot function will unfold your data into 2 new columns, Attribute and Value. Next rename "Attribute" to "Date" and then change the format of the Date field to be Date.
5. Next, I changed the Date values to be the End Of Month. This is optional. Then click Close and Load in the Home tab of the ribbon.
6. The result of the Power Query will be in a table in your excel file.
7. Now create the PIVOT table and put the Date field into the columns.
8. Finally, you can let the PIVOT table calculate the Difference from the previous month by changing the Value field. Right click on "Sum of Values" > Value Field Settings and then choose Show values as > Difference From and select your base field and item.