Forum Discussion
Get last Month End Data using Power Query
Here you go Marcel_
Here is what I did
- Grouped the data by product ID with two aggregations, one for all rows, one for max of date. That returned the table you see at the top.
- I expanded the table for the date and pallets which gives this:
-
- I then added a new column that compared Max Date with Date. If it is the same, return "Month End", otherwsie null. = if [Date] = [Max Date] then "Month End" else null
- THen I cleaned up the columns. Final result:
-
By using the Group By feature, you are partitioning the data per product ID. That will make it perform better over large data sets. On 1,000 records it won't matter, but above that and you'll see performance gains with Group By.
Full code here.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pallets", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BM"), #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Product ID"}, {{"All Rows", each _, type table [Product ID=nullable text, Date=nullable date, Pallets=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Pallets"}, {"Date", "Pallets"}), #"Added Month End" = Table.AddColumn(#"Expanded All Rows", "Month End", each if [Date] = [Max Date] then "Month End" else null, type text), #"Removed Other Columns" = Table.SelectColumns(#"Added Month End",{"Product ID", "Date", "Pallets", "Month End"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Pallets", Int64.Type}}) in #"Changed Type1"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.- Grouped the data by product ID with two aggregations, one for all rows, one for max of date. That returned the table you see at the top.
Try it using the Group By feature. Add a final aggregation that is "All Rows" - this will preserve all of your data and you can re-expand it at the end of the process. If that doesn't work, or you need help with this, let us know.
Hi edhans,
Thanks. I have tried the Group By feature but I am not really sure I am using it correctly, as I am trying to retrieve the last value at the end of each month.
i.e :
Product ID
Date Pallets Index Product-Month Concatenate Month End Date to use 2505 18/03/21 5 1 25053 18/03/21 2505 18/03/21 6 2 25053 18/03/21 2505 20/03/21 4 3 25053 Month End 20/03/21 33650 01/10/21 2 4 3365010 01/10/21 33650 21/10/21 33 5 3365010 Month End 21/10/21 20000 12/12/21 10 6 2000012 Month End 12/12/21 1000 01/02/22 5 7 10001 01/02/22 1000 01/02/22 5 8 10001 01/02/22 1000 01/02/22 7 9 10001 Month End 01/02/22 Thanks for the All Rows tip!
Regards,
Marcel
Here you go Marcel_
Here is what I did
- Grouped the data by product ID with two aggregations, one for all rows, one for max of date. That returned the table you see at the top.
- I expanded the table for the date and pallets which gives this:
-
- I then added a new column that compared Max Date with Date. If it is the same, return "Month End", otherwsie null. = if [Date] = [Max Date] then "Month End" else null
- THen I cleaned up the columns. Final result:
-
By using the Group By feature, you are partitioning the data per product ID. That will make it perform better over large data sets. On 1,000 records it won't matter, but above that and you'll see performance gains with Group By.
Full code here.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY1LCsAwCESvUlwHHMcaepeQ+1+j1kA/dHDhvAc6hjAQ0sQOhSttqyQIme1nc+1vTtx8L+7eA9lgaliCH8FHuK9TyFw/qDllDDLnCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Date = _t, Pallets = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pallets", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-BM"), #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Product ID"}, {{"All Rows", each _, type table [Product ID=nullable text, Date=nullable date, Pallets=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}), #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Pallets"}, {"Date", "Pallets"}), #"Added Month End" = Table.AddColumn(#"Expanded All Rows", "Month End", each if [Date] = [Max Date] then "Month End" else null, type text), #"Removed Other Columns" = Table.SelectColumns(#"Added Month End",{"Product ID", "Date", "Pallets", "Month End"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Pallets", Int64.Type}}) in #"Changed Type1"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.- Grouped the data by product ID with two aggregations, one for all rows, one for max of date. That returned the table you see at the top.
