Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Expression.Error in nested "if" statements

Hi all,

 

I am at a bit of a loss here and need your help.

I've been trying to write a fairly complex function in M and keep getting stuck at a point here.

 

Main target is to use a bunch of parameters and lists to extract a service time from StartDate to EndDate.

fxWorkingDays is a custom function that's working similar to "NetWorkdays" in Excel and delivers the raw number of days from StartDate to EndDate.
However, in order to get the service time, I need the value in [hh]:mm format, i.e. 48:30. Hence I need to fiddle around with the number a bit.

The "duration" type is what I am looking for, as it's providing the correct format, but I need to add and substract some things to the calculated time.

 

The error I keep getting is Expression.error: The name "ServiceTime" wasn't recognized. Make sure it's spelled correctly.

Let me paraphrase my code here - I will remove the unnecessary bits, as some of it is already working fine:

(StartDate as datetime, EndDate as datetime) as duration =>
let
	<variable declaration>	
	ServiceTime = if (
		<statement that returns "false" if the ServiceTime needs to be calculated>	) then (
		ServiceTime = #duration(0,0,0,0)
	) else (
		let
			// Calculate raw number of days and multiply it with the duration of "1 day"			C_ServiceTimeInDays = #duration(1,0,0,0) * fxWorkingDays(StartDate, EndDate, {holidays}),

			// Check if StartDate needs to be substracted. If yes, mark it as such			C_StartDateIsWorkDay = if
				<statement that returns "true">			then
				#duration(1,0,0,0)
			else
				#duration(0,0,0,0),

			// Check if EndDate needs to be substracted. If yes, mark it as such			C_EndDateIsWorkDay = if
				<statement that returns "true">			then
				#duration(1,0,0,0)
			else
				#duration(0,0,0,0),

			// At this point I know what I need to do (logically)
			// I need to return the "ServiceTime", which is the result of the outer "else" case:
// Therefore, I need to calculate the duration, which is the "ServiceTimeInDays". Logically, this is:
// "C_ServiceTimeInDays" minus "C_StartDateIsWorkDay" minus "C_EndDateIsWorkDay"
// However, this does not work and I get above error: ServiceTimeInDays = C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay in ServiceTimeInDays ) in ServiceTime

I believe my issue here lies in the "in" and returned statements, but I can't figure it out for the life of me...

 

I had also tried it with this, which brings another error message:

(StartDate as datetime, EndDate as datetime) as duration =>
let
	<variable declaration>
	
	ServiceTime = if (
		<statement that returns "false" if the ServiceTime needs to be calculated>
	) then (
		ServiceTime = #duration(0,0,0,0)
	) else (
<without the second "let ... in ...", but still has the same calculations> #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) ) in ServiceTime

// RESULT: "Expected Token: Identifier" at this point:
#duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
^

In this case, if I instead type

ServiceTime = #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay)
//RESULT: "The variable with the name "ServiceTime" is already defined in this area"

I really hope, someone can help...

  • Hi Anonymous,

    remove the part with "ServiceTime =" before empty duration. 

     

    (StartDate as datetime, EndDate as datetime) as duration =>
    let
    	<variable declaration>	
    	ServiceTime = if (
    		<statement that returns "false" if the ServiceTime needs to be calculated>	) then (
    		ServiceTime = #duration(0,0,0,0)

     

    Next point is

    #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) 

    which is definitely not correct, just use C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay.

     

    Btw. a similar task has been solved in Date / Time difference excluding weekends and factoring working ours, you can also inspire also there.

2 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar

    Hi Anonymous,

    remove the part with "ServiceTime =" before empty duration. 

     

    (StartDate as datetime, EndDate as datetime) as duration =>
    let
    	<variable declaration>	
    	ServiceTime = if (
    		<statement that returns "false" if the ServiceTime needs to be calculated>	) then (
    		ServiceTime = #duration(0,0,0,0)

     

    Next point is

    #duration(C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay) 

    which is definitely not correct, just use C_ServiceTimeInDays - C_StartDateIsWorkDay - C_EndDateIsWorkDay.

     

    Btw. a similar task has been solved in Date / Time difference excluding weekends and factoring working ours, you can also inspire also there.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for pointing me to that thread!
      I was able to adapt this to my needs, also leveraging some of what I did in my formula.