Group By with Filter in Power Query

[smko]

The SalesTable looks like this.

OrderDate OrderTime CustomerKey Status Sales
1/1/2021 13:00:00 1 Failed $50
1/1/2021 13:15:00 1 Success $50
1/1/2021 15:20:00 1 Success $100
31/1/2021 04:30:00 2 Failed $70
31/1/2021 04:35:00 2 Success $70
15/2/2021 08:00:00 2 Success $80
16/2/2021 20:00:00 3 Success $200
20/2/2021 18:00:00 3 Success $20
1/3/2021 01:00:00 3 Success $50

 I want it to be group by OrderDate and CustomerKey where Status = "Success", I put in Sales column in just for clarification. The expected result is 

OrderDate CustomerKey Sales
1/1/2021 1 $150
31/1/2021 2 $70
15/2/2021 2 $80
16/2/2021 3 $200
20/2/2021 3 $20
1/3/2021 3 $50

I can do this easily in DAX using SUMMARIZE( FILTER( SalesTable, Status = "Success" ), SalesTable[OrderDate], SalesTable[CustomerKey] ) but how do it in Power Query?

To add in more context, the above calculation is for identifying the period of gap (in days/months) between transactions for different days for each individual customer. The final goal will look something like this

OrderDate CustomerKey NextPurchase DayGap
1/1/2021 1
31/1/2021 2 15/2/2021 15
15/2/2021 2
16/2/2021 3 20/2/2021 4
20/2/2021 3 1/3/2021 9
1/3/2021 3

By now I can identify how long averagely does it takes for a given customer to buy again, i.e. Customer2 15 days, Customer 3 (4+9)/2 = 6.5 days

[v-kelly-msft · Accepted solution]

Hi  @smko ,

Using below M code:

let
    Source = Table.SelectRows(Table,each [Status]="Success"),
    #"Grouped Rows" = Table.Group(Source, {"OrderDate"}, {{"CustomerKey", each List.Max([CustomerKey]), type nullable number}, {"Sales", each List.Sum([Sales]), type nullable number}})
in
    #"Grouped Rows"

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[wdx223_Daniel]