Power Query Create New Column Based on Two Other Columns-CodePudding

Good morning,

I am trying to create a new column in Power Query that looks at the dates of two columns. Please see the below image,

I am trying to create a custom column giving a 1 or 0 based on the EffDate and DeliveryDate columns. I want to give a 1 if the DeliveryDate column is the first day of the forward month of the EffDate, then a 0 if not. So for example, in line 1, for 12/31/2019, I want 1/1/2020. Using the function,

= Table.AddColumn(#"Renamed Columns", "Prompt Month", each if [DeliveryDate] = #date(Date.Year([EffDate]), Date.Month([EffDate]) 1, 1) then 1 else 0)

does not work.

CodePudding user response：

Date.Month([EffDate]) 1 when [EffDate] is December is an error. There is no month after december

Try

if Date.AddMonths(Date.StartOfMonth([EffDate]),1) = [DeliveryDate] then 1 else 0