Excel Power Query: custom column with difference from previous month

Question

Hi,

I have a set of data where where I need to add a column with the change in volume from the previous month for each Customer and for each Lane. I attached a photo to help demonstrate what I mean. Is there any way to create a custom column with the values being the difference in volume from the previous month for that particular customer and lane?

Answer 1

Hi

If you must do it in Power Query here is one way (can be slow if you have thousandssss of records) assuming:

• The step before adding your column is ChangedTypes
• Your [Date]s are always month/01/year (as in your pic.) otherwise an ajdustment is required

Corresponding sample available here

Note: I've taken the freedom to revise the title of this thread to better reflect the challenge (can help those who search)

Answer 2

IMHO a simple Pivot table is enough to answer this.

Sample file

https://www.dropbox.com/s/a1izxk0qgs6y0qz/9e22fdf7-02d8-4e4c-a1a6-c88486852b65.xlsx?dl=1

Andreas.

Answer 3

Hi,

I believe it is easier to do this calculation in the Data Model. So you should load this data to the Data Model and then write a calculated column formula to get your desired result. If you really want to stick to a Query Editor solution, then too this can be done but will not be as easy as the Data Model solution.

Either ways, share the download link of your MS Excel file and in a worksheet show your expected result very clearly.