Summarizing table sales

May 6, 2022

I have order table like

CustomerId  OrderAmount Orderdatetime   OrderAmountCurrency
AAA 120 02/03/2022 02:03    US$120
AAA 20  02/03/2022 02:20    US$20
AAA 320 03/03/2022 03:03    US$320
BBB 300 02/03/2022 02:03    $300
BBB 20  02/03/2022 02:20    $20
BBB 200 02/03/2022 03:03    $200

I’d like to display a table like this: Customerid, initial order-The amount of the first attempt All purchases worth less than $ 40 should be excluded, Ordedate-the order of the first attempt; Final Order- the lastest attempt

CustomerId  InitialOrder    Orderdate   FinalOrder
AAA US$120  02/03/2022 02:03    US$320
BBB $300    02/03/2022 02:03    $200

How to do it?

>Solution :

In powerquery, it requires grouping on CustomID, with a bit of custom code as below

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Orderdatetime", type datetime}, {"OrderAmount", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerId"}, {
    {"Initial Order", each Table.Sort(_,{{"Orderdatetime", Order.Ascending}}){0}[OrderAmount]},
    {"Initial Order Date", each Table.Sort(_,{{"Orderdatetime", Order.Ascending}}){0}[Orderdatetime]},
    {"Last Order", each Table.LastN(Table.Sort(_,{{"Orderdatetime", Order.Ascending}}),1){0}[OrderAmount]}
    }),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Initial Order Date", type datetime}, {"Last Order", type number}, {"Initial Order", type number}})
in  #"Changed Type1"