Forum Discussion
Solved
Subtracting two queries in Power Query
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).
- 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).
- 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!
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.
2 Replies
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.
