M Code: Create new column that populates with last day of current month

[Anonymous]

Hi,

I'm bringing in a delimited text file/table in to Power BI.  This table has medical eligibility data (names, blood type, age, and medical insurance coverage dates).

One of the fields is named [Coverage End Date].  This column is usually populated with a date that I can work with however it is sometimes left blank if an employee is still with the company (if the employee is still with the company it is impossible to know when their medical insurance eligibility will end).  Here is an example of the data:

Member ID    Name               Age     Coverage Start Date     Coverage End Date

0100              Jim Kelly           50        1/20/2010                    5/30/2018

0202              Tim Tebow       20        4/15/2008                    9/3/2010

0555              John Elway       55        2/19/2006                  

0177              Tom Brady       96        6/10/2011                    6/3/2019

I would like to create a new column (using Power Query while using M Code) that essentially does the following:

If [Coverage End Date] has a date, then use that date but if [Coverage End Date] is empty use the last date of the current month.  For example, I'd need John Elway's [Coverage End Date] to populate with 9/30/2020 if ran today.

This dashboard and the tables will be refreshed several times throughout the month and many times througout the year so I need this new column to have this new date column fluid.

Any ideas?

Thank you!

[Greg_Deckler · Accepted solution]

@Anonymous - Perhaps:

End =
  if [Coverage End Date] <> null then [Coverage End Date] else Date.EndOfMonth([Coverage End Date])

[amitchandak]

@Anonymous , Try like

if [Coverage End Date] = null then Date.EndOfMonth(DateTime.LocalNow()) else [Coverage End Date]

http://excel-inside.pro/blog/2018/05/17/comparing-null-values-in-power-query/