How to get Customer wise Last Order Quantity in Power Query

[Gopal_PV]

Hi Friends,

I have customer table with Details .

I want to get Each Customer wise last order Quantity in Power Query along with all other columns .

Can you please help me how to do this in Power Query

[v-hjannapu]

Hi @Gopal_PV,

Thank you for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @SundarRaj, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

1.  First, ensure your OrderDate column is of date type.

 2. Then, sort the table by the OrderDate column in descending order so that the latest dates come first.

3. Use the Group By feature on the CustName column and choose the "All Rows" operation. This will create a     nested table for each customer.

 4. After that, add a custom column to extract the top row from each nested table, which represents the latest order per customer.

 5. Finally, expand the resulting column to bring back all the original fields like OrderQuantity, Price, and OrderDate.

find the attached pbix file and screenshot for your reference

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.
Community Support Team

[SundarRaj]

Hi @v-hjannapu,
Thanks a lot for the kind mention! Glad to contribute and be a part of this great community.

Best,

[SundarRaj · Accepted solution]

Hi @Gopal_PV ,
By last, I am assuming you mean the latest order quantity as per the OrderDate. Correct me if I am wrong. I'll attach the image of output and the M code used.

Here's the code:
let
Source = #table(
{"Custid", "OrderDate", "CustName", "OrderQuantity", "Price"},
{
{1, #date(2025, 10, 1), "A", 5, 200},
{1, #date(2023, 2, 1), "A", 7, 300},
{2, #date(2023, 2, 1), "B", 8, 500},
{2, #date(2024, 1, 1), "B", 10, 700},
{2, #date(2023, 5, 1), "B", 22, 1000},
{3, #date(2021, 5, 1), "C", 15, 800},
{4, #date(2022, 5, 1), "D", 18, 1200},
{3, #date(2022, 6, 1), "C", 19, 1800},
{4, #date(2025, 5, 1), "D", 20, 1500},
{5, #date(2022, 1, 1), "E", 15, 1300},
{5, #date(2022, 5, 1), "E", 16, 2000},
{4, #date(2025, 1, 1), "F", 12, 1000},
{4, #date(2022, 5, 1), "F", 13, 800}
}
),
#"Grouped Rows" = Table.Group(Source, {"CustName"}, {{"Table", each _, type table [Custid=number, OrderDate=date, CustName=text, OrderQuantity=number, Price=number]}}),
Table = Table.TransformColumns ( #"Grouped Rows" , { "Table" , each Table.FirstN ( Table.Sort ( _ , { "OrderDate" , Order.Descending } ) ,1 ) } )[[Table]],
#"Expanded Table" = Table.ExpandTableColumn(Table, "Table", Table.ColumnNames ( Source ) , Table.ColumnNames ( Source ) )
in
#"Expanded Table"
In case you don't want the Latest OrderQuantity but the opposite, just change the Order.Descending to Order.Ascending in the Table step. Thanks

[Cookistador]

Hi @Gopal_PV 

You can achieve that by following these steps:

• Sort the data:
  • click on OrderDate.
  • Go to Sort → Sort OrderDate by Descending (latest first).
• Remove duplicates to get last order
  • Right click on custID and select remove dupplicate

This is the M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLDoMwDATQu2QNkj8Y6LKl7SUQ978GGdepTNqFhaK8jG32vXAZCvHINAqJ1cO9Fr5CVI7hC0gANMBSSwNIDx61VqR0gAGmAEyI6YTlCBFnQTQTzLThFoOuIaYs8PYJgUFYupC5EQ+5ufuXYpEi5M2CWN4HKa82CmtH7ELmz2+9NuLW6O2T5p1/NnKibefjBA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custid = _t, OrderDate = _t, CustName = _t, OrderQuantity = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Custid", Int64.Type}, {"OrderDate", type date}, {"CustName", type text}, {"OrderQuantity", Int64.Type}, {"Price", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"OrderDate", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"CustName"})
in
    #"Removed Duplicates"