Forum Discussion
Power Query Subtract one column from Another
Hi Experts
How would you subtract one column from another in a cross tab table in Power Query - see image Below
Date and Region columns remain as is - i want to subtract in a new table 14/09/20 - 21/09/20, then 21/09/20 - 28/09/20 and so on until the last column in the cross tab table
Image/
| Date | Region | 14/09/2020 | 21/09/2020 | 28/09/2020 | 05/10/2020 |
| 01/01/2019 | Scotland | 6613 | |||
| 01/02/2019 | Scotland | 6716 | |||
| 01/03/2019 | Scotland | 7171 | |||
| 01/04/2019 | Scotland | 7295 | |||
| 01/05/2019 | Scotland | 5719 | |||
| 01/06/2019 | Scotland | 5499 | |||
| 01/07/2019 | Scotland | 5064 | |||
| 01/08/2019 | Scotland | 5050 | 5072 | 6070 | |
| 01/09/2019 | Scotland | 7290 | 6095 | 6621 | |
| 01/10/2019 | Scotland | 5345 | 5300 | 6040 | |
| 01/11/2019 | Scotland | 7043 | 5580 | 5194 | |
| 01/12/2019 | Scotland | 5871 | 6741 | 7468 | |
| 01/01/2020 | Scotland | 6941 | 6004 | 6450 | |
| 01/02/2020 | Scotland | 7349 | 6840 | 5715 | |
| 01/03/2020 | Scotland | 6398 | 6991 | 5746 | |
| 01/04/2020 | Scotland | 5786 | 6307 | 7348 | |
| 01/05/2020 | Scotland | 5067 | 6675 | 5217 | |
| 01/06/2020 | Scotland | 6502 | 6110 | 5291 | |
| 01/07/2020 | Scotland | 5477 | 6672 | 6534 | |
| 01/08/2020 | Scotland | 5895 | 5550 | 5420 | |
| 01/09/2020 | Scotland | 7150 | 6096 | 5354 | |
| 01/10/2020 | Scotland | 5232 | 5881 | 6235 | |
| 01/11/2020 | Scotland | 6267 | 6449 | 5699 | |
| 01/12/2020 | Scotland | 5608 | 7005 | 7123 |
2 Replies
Anonymous , unpivot the table
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
and then create a table with distinct dates and create a rank and then you can this period vs the last period
Rank 1 = RANKX('Date','Date'[Date],,ASC,Dense)
This period= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Rank 1]=max('Date'[Rank 1])))
Last period= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Rank 1]=max('Date'[Rank 1])-1))
