Search code examples
powerbipowerquerypowerbi-desktop

PowerQuery - add column with end of month where the last day is not included in the data

I have a table with dates and would like to add a column to display the end of the month.

Normally this is simple, but my problem is, in some cases the last day of the month is not included in the data. I would like to add the last day available in the report as the last day of the month:

Date format: mm/dd/yy

Date End Of month What I need
01/10/2023 31/10/2023 20/10/2023
06/10/2023 31/10/2023 20/10/2023
10/10/2023 31/10/2023 20/10/2023
20/10/2023 31/10/2023 20/10/2023
05/09/2023 30/09/2023 29/09/2023
16/09/2023 30/09/2023 29/09/2023
29/09/2023 30/09/2023 29/09/2023

Is there a simple way to achieve that?

Thank you for any help.

Solution

  • In powerquery (date format irrelevant)

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastDate",  (x)=>List.Max(Table.SelectRows(#"Changed Type", each Date.EndOfMonth(x[Date])=Date.EndOfMonth([Date]))[Date]),type date)
in #"Added Custom"

    enter image description here

    alternate

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"End of Month"}, {{"Max Date", each List.Max([Date]), type nullable date}, {"data", each _, type table [Date=nullable date, End of Month=date]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date"}, {"Date"})
in  #"Expanded data"