SOLVED

Subtracting two queries in Power Query

Copper Contributor

cBi2aC9BUQ.png

The picture above shows my data after I've performed some manipulations on it in PQ.

The data is after a "Pivot Column" step is aggregated as maximum ,and it represents the maximum number of fruits at certain time periods (each letter represents a time period, i.e. B1, C1 etc).

 

  1. how can I subtract the entire table by a number? it'll be a parameter, but let's say that I want to subtract 3 from every cell in the data (except for the letters, obviously).
  2. suppose I duplicated the query and instead of using maximum, I've chosen to aggregate as minimum. Now I want to know what is the largest margin for every cell, so I want to subtract the minimum query from the maximum query. How can I do that?

 

Unfortunately, I can't know upfront which fruits or which time periods are going to be in the data, so it has to be completely dynamic.

 

I'll be glad for some ideas how to approach this issue...

 

Thanks!

2 Replies
best response confirmed by AmyM-NirR (Copper Contributor)
Solution

@AmyM-NirR Please find solutions for both in the attached file. 

 

Re 1) I recently picked-up a technique to do this  on the microsoft.answers forum. You'll find the link in the file.

Re 2) I came up with a rather simple, all GUI (i.e. no M-coding), solution. See if it works for you.

@AmyM-NirR 

Another Power Query option attached

1 best response

Accepted Solutions
best response confirmed by AmyM-NirR (Copper Contributor)
Solution

@AmyM-NirR Please find solutions for both in the attached file. 

 

Re 1) I recently picked-up a technique to do this  on the microsoft.answers forum. You'll find the link in the file.

Re 2) I came up with a rather simple, all GUI (i.e. no M-coding), solution. See if it works for you.

View solution in original post