How can I summarize a table based on the last day of the month

Question

I have an input table in PowerBI like this:

Date	id
10/17/2022	4343
10/18/20222	3169
11/23/2022	4681
12/23/2022	6542
12/16/2022	534324
1/30/2023	4343
1/18/2023	35325

I want to create a 'Summary' Table using which just keep the row for the most recent date of each month.

Meaning the output is

Date	id
10/18/20222	3169
11/23/2022	4681
12/23/2022	6542
1/30/2023	4343

Answer 1

Starting table:

Highlight date column and then add year and month from the ribbon.

Group by as follows.

Merge back to source and expand.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3BDYAwDEPRXXKulMZOSpml6v5rQBWK4Ppk+Y8hVtUORQWkiNMps6T2rbR2ppqC77Z1S8VHWzi2Wtsa9y88XVkX85d7agsZRMicFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, id = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
    #"Inserted Year" = Table.AddColumn(#"Changed Type with Locale", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Year", "Month"}, {{"Date", each List.Max([Date]), type nullable date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Year", "Month"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Date"}, #"Changed Type with Locale", {"Date"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"id"}, {"id"})
in
    #"Expanded Removed Columns"