Forum Discussion

danialsj's avatar
danialsj
Frequent Visitor
6 years ago
Solved

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.

 

CodeDateAmount
100131/1/1910
100131/1/1920
100128/2/1915
100128/2/1925
100231/1/1930
100231/1/1940
100228/2/1915
100228/2/1925

 

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'

 

CodeDateSubTotal
100131/1/1930
100128/2/1940
100231/1/1970
100228/2/1940

 

I need to do this in Power Query.

 

Thank you!

  • hi danialsj 

    that's pretty much easy

    right click on the Code field. then "Group by" then Advanced then Add grouping and Sum of Amount as new column. like this

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

     

1 Reply

  • az38's avatar
    az38
    Community Champion

    hi danialsj 

    that's pretty much easy

    right click on the Code field. then "Group by" then Advanced then Add grouping and Sum of Amount as new column. like this

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn