Forum Discussion

smko's avatar
smko
Helper I
5 years ago
Solved

Group By with Filter in Power Query

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

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!