Replacing Text with Day Number

[Anonymous]

Hello everyone,

Could someone detect what is the issue with the below script?

I got 2 columns, Date, which got list of dates and the type is just Date, and Monthly Reports Days List" that got whole numbers but there are entries that have the text “End”, and of course the type is any.

I am trying to replace that “End” text entries with the end of the month day number, yet, I’m getting and error.

That is the script:

= Table.ReplaceValue(#"Expanded Monthly Reports Days List","End",Date.Day(Date.EndOfMonth([Date])),Replacer.ReplaceValue,{"Monthly Reports Days List"})

That is the error:

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

How to sort this out?

[watkinnc]

You could probably just add a column by clicking on your date column, then add a column using the GUI date section option "Day of the Month", which uses Date.Day.

--Nate

[wdx223_Daniel · Accepted solution]

= Table.ReplaceValue(#"Expanded Monthly Reports Days List","End",each Date.Day(Date.EndOfMonth([Date])),Replacer.ReplaceValue,{"Monthly Reports Days List"})