Get last Month End Data using Power Query

Here you go Marcel_

Here is what I did

1. 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.
   1. 
2. I expanded the table for the date and pallets which gives this:
   1. 

       

3. 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
4. THen I cleaned up the columns. Final result:
   1. 

       

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.