Forum Discussion
danialsj
6 years agoFrequent Visitor
Power Query - Filtering rows and adding a column for subtotals based on two columns
Data:
Each 'Code' has multiple 'Amount' in a month. E.g. 1001 has two entries of 10 and 20 on 31st January.
| Code | Date | Amount |
| 1001 | 31/1/19 | 10 |
| 1001 | 31/1/19 | 20 |
| 1001 | 28/2/19 | 15 |
| 1001 | 28/2/19 | 25 |
| 1002 | 31/1/19 | 30 |
| 1002 | 31/1/19 | 40 |
| 1002 | 28/2/19 | 15 |
| 1002 | 28/2/19 | 25 |
Requirement:
I want to make my data unique on the basis of 'Code' and 'Date' so that each 'Code' has one entry for each 'Date' and the 'Amount' is totalled up into a new column 'SubTotal'
| Code | Date | SubTotal |
| 1001 | 31/1/19 | 30 |
| 1001 | 28/2/19 | 40 |
| 1002 | 31/1/19 | 70 |
| 1002 | 28/2/19 | 40 |
I need to do this in Power Query.
Thank you!
1 Reply
- az38Community Champion