Hi,
I have a data source that gives me monthly sales values by customer. The data is in the following format:
Customer Name | 01.2017 | 02.2017 | 03.2017 ......
I want to add a column that gives me the average sales for all of the periods in the report. I know I could add a custom column where I could insert the column names and divide it by the number so for the above it would be something like:
([01.2017]+[02.2017]+[03.2017])/3
But how do I cope with this when the source data contains new months? When 04.2017 is added I want the sales for this month to be added in and then divide by 4. I am hoping that I don't need to manually update the query every month to handle this.
Any ideas greatly received as always.
Thanks
Bax
Hi Bax,
This isn't a job for Power Query. In Power Query you should unpivot your data so it's in a tabular layout. In other words you shouldn't have separate columns for the months, there should be a column for the month/date and a column for the values, as well as the Customer Name column.
Then you can use a PivotTable to calculate the average for the last 3 months.
Mynda
Hi,
I managed to find a couple of ways to do this in power query, one is to create a query that calculates the averages and then merge that query with the original data. The other uses PQ to unpivot the source data, returns that table to Excel, and then uses a pivot table to calculate the average.
I have attached a file that shows both versions.
Thanks
Bax
In my opinion the PivotTable is the correct approach because now you have your data in the correct tabular format you can do much more than just calculate the averages with it, should you ever wish to. With the Power Query layout it's already pivoted and will be difficult to do anything else with that data.
Mynda
