Search code examples
if-statementpowerquerydate-range

Get output within multiple date ranges (and if statements) in Power Query

Edited to include full code with the Add Column function in Power Query. The "Removed Columns1" refers to the previous step, and name the custom column as Price.

I'm trying to add a custom column in Power Query to output a price for specific date ranges. Tried the following M code but I get an error instead:

= Table.AddColumn(#"Removed Columns1", "Price", each
    if [Date]<#date(08,12,2019) then "11.50"
    else if [Date]>#date(08,11,2019) and [Date]<#date(10,14,2019) then "0.00"
    else if [Date]>#date(10,13,2019) and [Date]<#date(12,30,2019) then "11.50"
    else "2.50")

The error I get back:

Expression.Error: The Date operation failed because the resulting value falls outside the range of allowed values.

Solution

  • Use #date(2019,08,11) not #date(8/11/2019):

    = Table.AddColumn(#"Removed Columns1", "Price", each
    if [Date]<#date(2019,8,12) then "11.50"
    else if [Date]>#date(2019,8,11) and [Date]<#date(2019,10,14) then "0.00"
    else if [Date]>#date(2019,10,13) and [Date]<#date(2019,12,30) then "11.50"
    else "2.50"
)