I know I can do this easily enough in a pivot table, but can I use power query to figure out the monthly difference? I'm looking to figure out Costs, Revenue and Profit. So Jan Cost $400 and the next row shows Feb Cost is $500, then I'd have a cost difference column so that Jan is $0 (since there isn't anything before Jan) and Feb difference is $100. I'd also make a column for % diff.
I'd like to use the difference as a KPI on a dashboard. If I use an unfiltered pivot table, then it shows me the difference, but if I use a slicer to choose one month, then I lose the information for the difference. It comes up as blank. So, I thought it might be better to calculate the difference in Power Query before I make it into a pivot table and slice it by a month so that I have the info.
Hi John,
Yes you can do this. Columns in Power Query are lists so you can access items in that column/list using an index (starting at 0 for the first element).
So, add an Index column, then refer to the relevant data in each row using the numbers in the Index column, for example, to access the value 500 for Feb in the Cost column you'd use [Cost]{1}
A generalized version of that code to get the Cost value for each row would be [Cost]{[Index]}
You can add a Custom Column to get the Cost Difference using
if [Index] > 0 then #"Added Index"[Cost]{[Index]} - #"Added Index"[Cost]{[Index] - 1} else null
and to get the Cost Difference %
if [Index] > 0 then (#"Added Index"[Cost]{[Index]} - #"Added Index"[Cost]{[Index] - 1}) / #"Added Index"[Cost]{[Index]} else null
See attached file for an example of what you requested. Please note that when calculating the Cost Difference % I didn't know if you wanted that as a % of the previous month or the current month. I assumed previous month, it's easy to change.
Regards
Phil
@philipt Getting this result in PQ always confused me. Thank you for explaining it so well in this post. I think I finally get it!
