Forum Discussion
How to get Customer wise Last Order Quantity in Power Query
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
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"