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

[gcoyle97]

Hi All, can someone help with the error I'm having on Power BI query in the subject.

I have looked at similar errors but none seem to work for me.

=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"))

= Table.AddColumn(#"Reordered Columns5", "OTD", each 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")))

[v-stephen-msft]

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

[BA_Pete]

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

[m_dekorte]

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

[gcoyle97]

Hi, I have followed your instuctions with adding the fuction code into a blank query and using the code.

However, I still have the below error.

I know this is easy to fix I just havent got much experiece yet.

[m_dekorte]

Hi @gcoyle97,

yes I missed a parenthesis, try this:
if (fnNETWORKDAYS([PromisedDeliveryDate], [Ship Date]) < 1) then
Text.From(Number.Abs((fnNETWORKDAYS([PromisedDeliveryDate], [Ship Date]) + 1))) & " Day(s) Faster then Promised"
else if (fnNETWORKDAYS([PromisedDelivryDate], [Ship Date]) < 1) then
Text.From(Number.Abs((fnNETWORKDAYS([PromisedDeliveryDate], [Ship Date]) - 1))) & " Day(s) Late"
else
"On Time"

[gcoyle97]

Thanks @m_dekorte this give me no syntax errors.

But I know have errors in all my cells.

I think this is maybe something to do with the fnNETWORKDAYS query I created?

[m_dekorte]

Did you open the Advanced Editor and replaced its contents with the code for the Custom Function that was shared? It looks like a string, you may have copied it into the formula bar instead...