Replace Dates at Beginning of Month with Last Day of Previous Month

[MBenson415]

Hello,

I am attempting to use Power Query to do the following in plain langue: If a date appears at the beginning of the month, replace it with the last date of the previous month (essentially, push it back one day). Leave all other dates alone.

So far, I have deduced the following:

= if Date.Day([End Current]) = 1 then 
Table.ReplaceValue(#"Changed Type"
,each [End Current]
,each Date.AddDays([End Current], -1)
,Replacer.ReplaceValue,{"End Current"}) else 
[End Current]

But I am receiving the error "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

And I can't trace it.

Anybody have an idea about how to resolve?

FYI: Date.Day function returns a numeric for the date referenced, and Date.AddDays adds or removes an integer number of days according to the argument given (in this case, -1).

What am I missing?

THANK YOU!

[FarhanAhmed1984 · Accepted solution]

Can you please try this... Replacer.ReplaceValues may have some issues in conditional replace

#"OutP" =
Table.RenameColumns(
    Table.RemoveColumns(
        Table.AddColumn( #"Changed Type", "NewDate",
        each if Date.Day([End Current]) = 1 then
                    Date.AddDays([End Current], -1) else [End Current]),
         {"End Current"}),{{"NewDate", "End Current"}})