Forum Discussion

Gopal_PV's avatar
Gopal_PV
Helper III
1 year ago
Solved

How to get Customer wise Last Order Quantity in Power Query

 

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

 

 

 

 

 

Tips and Tricks
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    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

4 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    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"

     

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    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

  • v-hjannapu's avatar
    v-hjannapu
    Community Support
    1 year ago

    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's avatar
      SundarRaj
      Super User
      1 year ago

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

      Best,