Power Query - Filter field by max date per month

[cottrera]

I have a table called hitorical_repairs this table updates daily  and we have a field called DateAdd which informs us of the update date. Occationally the datawarehouse falls down and we lose a day of data but thats fine.

I would like help gettting power query to filter the hitorical_repairs table by the DateAdd field to show only the last date in each month. Below is an example of the dates I would like the table filtered by. As you will notice Jan 21 is missing the last two days and May 21 is missing the last day when the data warehouse fell down. Therefor the power query solution will need to take this into account and I guess look for the max date per month ?

thank you RIchard

DateADDED	Max Date
29/01/2021	Max
28/02/2021	Max
31/03/2021	Max
30/04/2021	Max
30/05/2021	Max
30/06/2021	Max
31/07/2021	Max
31/08/2021	Max
30/09/2021	Max
31/10/2021	Max
23/11/2021	Max

[cottrera]

Thank you KNP your solution works

[KNP · Accepted solution]

Hi,

Paste the below into the advanced editor of a blank query to analyze the steps.

All I did was add a month column, group by month, aggregate max date.

Unless I'm missing something, this should give you what you need.

let
  Start = DateTime.Date(Date.StartOfYear(DateTime.FixedLocalNow())),
  End = DateTime.Date(DateTime.FixedLocalNow()),
  Dates = {Number.From(Start) .. Number.From(End)},
  #"Converted to Table" = Table.FromList(
    Dates,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  ),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", type date}}),
  #"Filtered Rows" = Table.SelectRows(
    #"Changed Type",
    each (
      [Column1]
        <> #date(2021, 1, 30) and [Column1]
        <> #date(2021, 1, 31) and [Column1]
        <> #date(2021, 5, 31)
    )
  ),
  #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows", {{"Column1", "Date"}}),
  #"Inserted Month" = Table.AddColumn(
    #"Renamed Columns",
    "Month",
    each Date.Month([Date]),
    Int64.Type
  ),
  #"Grouped Rows" = Table.Group(
    #"Inserted Month",
    {"Month"},
    {{"MaxDate", each List.Max([Date]), type nullable date}}
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows", {"Month"})
in
  #"Removed Columns"

Hope this helps.