Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
4 years ago
Solved

Power Query - Filter field by max date per month

 

 

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

 

DateADDEDMax Date
29/01/2021Max
28/02/2021Max
31/03/2021Max
30/04/2021Max
30/05/2021Max
30/06/2021Max
31/07/2021Max
31/08/2021Max
30/09/2021Max
31/10/2021Max
23/11/2021Max
Need Help
  • KNP's avatar
    KNP
    4 years ago

    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.

     

2 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    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.