Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly

Hi gcoyle97,

 

You appear to have used DAX expressions. Keywords in M are written in lower case, hence the error message, M is case sensitive. There are other problems with your code as well, for example there is no standard library function NETWORKDAYS in M, but there are custom function's such as Imke Feldmann's here. ABS = Number.Abs and you have to use explicit type conversion. So this:

IF((NETWORKDAYS([PromisedDeliveryDate],[Ship Date]))<1,(ABS((NETWORKDAYS([PromisedDeliveryDate],[Ship Date])+1)))&" Day(s) Faster then Promised",IF((NETWORKDAYS([PromisedDeliveryDate],[Ship Date]))>1,(NETWORKDAYS([PromisedDeliveryDate],[Ship Date])-1)&" Day(s) Late","On Time"))

 

Can be translated to the code below. IMPORTANT! First, grab the custom function code from Imke's site, copy it into a new blank query, named: fnNETWORKDAYS Next change your custom column logic to:

 

if (fnNETWORKDAYS( [PromisedDeliveryDate], [Ship Date] ) <1) then Text.From( Number.Abs( (fnNETWORKDAYS([ PromisedDeliveryDate], [Ship Date] ) +1) ) & " Day(s) Faster then Promised" else if (fnNETWORKDAYS( [PromisedDeliveryDate], [Ship Date] ) <1 ) then Text.From( Number.Abs( (fnNETWORKDAYS([ PromisedDeliveryDate], [Ship Date] ) -1 )) &"  Day(s) Late" else "On Time"

 

I hope this is helpful

Hi gcoyle97 ,

 

The issue here is that you're trying to pass a DAX language calculation into Power Query, which uses M language.

Power Query doesn't have a NETWORKDAYS native function, but I've used the following M function before to good effect, courtesy of ImkeF.

 

// fnNETWORKDAYS
let func =   
(StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
let
    // optional StartOfWeek, if empty the week will start on Monaday
    startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
    
    // Providing for logic where EndDate is after StartDate
    Start = List.Min({StartDate, EndDate}),
    End = List.Max({StartDate, EndDate}),

    // Switch sign if EndDate is before StartDate
    Sign = if EndDate < StartDate then -1 else 1,

    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)),

    // if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
    // otherwise continue with previous table
    DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),

    // Select only the first 5 days of the week 
    // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
    DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),

    // Count the number of days (items in the list)
    CountDays = List.Count(DeleteWeekends) * Sign
in
    CountDays

 

 

Pete

Hi gcoyle97 ,

 

I reviewed this post and found that no replies seems to be accepted as solutions.

I saw both m_dekorte and BA_Pete have pointed out the crux of the mistake, has your problem been solved?

If possible, please accept them and more people will benefit from them.

 

Best Regards,

Stephen Tao