How to Use the Pivot Table Transform

A pivot table is a tool that allows you to discover, summarize and explore your data interactively. Pivot tables can sort, count, unique count, average, or find the minimum or maximum of your data, and display the results in a new table showing the summarized data. This gives you a lot of flexibility and analytical power to extract the significance from detailed data sets.

Here is an example:

You have a list of closed deals and you want to see your total MRR by sales rep and by date. We can answer this question quickly using a pivot table.

The Row Source will group the data from the selected column into rows, which will make it easier to select as your chart's category labels. If you are using dates, most of the time you will want to use that as the Row Source, grouped by day, week, month, quarter, or year.

The Column Source will group the data from that column into columns, which will make it easier to select as individual value series.

The Value Source is what will populate the rest of the table with the numbers you want to aggregate by the row and column sources.

If you are using the Row or Column source with a date grouping, you will have the option to "fill in missing dates," which will insert a row or column for the date group even if there is no data for that time period. When you are grouping by week you will also have the option to calculate the week starting on a Monday, instead of the default of starting on a Sunday.

There is also an option under the four fields to "auto fill blanks with 0," which will give the value of zero for any cells with no data. For instance, in our example if a sales rep did not make any sales on a specific day, the data would come in blank for that rep on that day, but we could display a zero to show that the revenue that day was zero.

In our example we want CloseDate as the Row Source, and Sales Rep in the Column Source. The Value Source is the MRR column, and we want to do a Sum as our Calculation Type. If you wanted to see the total number of sales by Sales Rep, rather than the dollar amount, you could change the Calculation Type to a Count, instead.