Power Query Create New Column Based on Two Other Columns

Question

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.

horseyride · Accepted Answer · 2022-09-26 17:09:19Z

3

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

edited Sep 26, 2022 at 17:09

answered Sep 26, 2022 at 17:03

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Power Query Create New Column Based on Two Other Columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related