cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Expression.Error in nested "if" statements

Hi @_n_MarianLein,

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.

View solution in original post

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: Expression.Error in nested "if" statements

Hi @_n_MarianLein,

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.

View solution in original post

Highlighted
Helper I
Helper I

Re: Expression.Error in nested "if" statements

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors