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

Check for NULL value in Formula

In a SharePoint connector I have a field that does a calculation on dates that come from an Excel spreadsheet. All works well until it hits a record where one or more dates are empty. How would I ask the formula to check for NULL and only do the calculation if something is present. The formula is as follows:

 

addDays('1899-12-30',int(outputs('Compose')),'MM/dd/yyyy')

 

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions

@TomDennison_BDP  Please try this-

if(empty(outputs('Compose')),null,addDays('1899-12-30',int(outputs('Compose')),'MM/dd/yyyy'))

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh

View solution in original post

Hi @Hardesh15   I gave your fix a try and I am still erroring out when I hit a blank date in the Excel file. The following is the output that I think would apply to the error. Any help would be appreciated!

 

Data From Excel Where it Failed

 

"@odata.etag": "",

"ItemInternalId": "5d18dfe8-31ee-4985-af2b-d252a03ec6c0",

"RP ID": "59048358",

"RP Revised Due Date": "",

"RP Completion Date": "43496",

 

Output Where RP Revised Due Date would populate

TomDennison_BDP_0-1612287030391.png

 

Error Where Compose 4 Would be Used

TomDennison_BDP_1-1612287030391.png

 

 

Design Time Use of Compose 4

 

TomDennison_BDP_2-1612287030391.png

 

 

Design Time Expression for “Revised Due Date”

 

TomDennison_BDP_3-1612287030391.png

 

 

 

View solution in original post

10 REPLIES 10
Pstork1
Dual Super User
Dual Super User

The easiest way to prevent that is to surround the date with Coalesce().  Coalesce lets you provide multiple parameters and the first one that evaluates to non-null is used.  

addDays('1899-12-30',int(coalesce(outputs('Compose'),0)),'MM/dd/yyyy')

if the outputs of the Compose is null then a 0 will be used instead.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi:

 

I've added the code you mention and I am still getting the same error when I hit the first record from Excel where the date field is empty. I first thought this might be because we are passing a 0 so I put it in quotes to make it a string and that did not work either.

 

The error I get with 0 as a string or int is:

The Template language function  'int' was invoked with a parameter this is not valid. The value cannot be converted to the target type.

 

Background:

  • Value in Excel is in String format and if present would be represented as a date serial number 43811 which process fine with the original formula without the coalesce.
  • The value is seen as a String in the Compose step

Thoughts?

 

@TomDennison_BDP  Please try this-

if(empty(outputs('Compose')),null,addDays('1899-12-30',int(outputs('Compose')),'MM/dd/yyyy'))

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh

There is another possible reason why you are getting the error.  A serial date in Excel is a whole number where the decimal portion represents time.  The INT() function in Power Automate can't process a whole number.  It can only take a string that is an int and cast it to an int.  In other words if your serial date comes in as something like 4789.125 then you'll get an error with int().

 

Is the output of the Compose step actually a null or is it a whole number? Because in my tests the formula worked if the Compose was a whole number with no decimal or null, but failed with the error you were getting if there was any fraction.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
TomDennison_BDP
Helper I
Helper I

Please see below for the output from a run that worked (Compose has a whole number) and one that failed (Compose has nothing, and I assume its NULL)

 

value.pngnull.png

You don't show the action that failed in the second screenshot.  But assuming it is the one in that loop using the Compose 4, then the value is Blank not null.  If that is the case then you need to use @Hardesh15 solution instead of mine.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

That would be the case. Sorry I did not explain it a bit better. I will give @Hardesh15 's solution a try.

Hi @Hardesh15   I gave your fix a try and I am still erroring out when I hit a blank date in the Excel file. The following is the output that I think would apply to the error. Any help would be appreciated!

 

Data From Excel Where it Failed

 

"@odata.etag": "",

"ItemInternalId": "5d18dfe8-31ee-4985-af2b-d252a03ec6c0",

"RP ID": "59048358",

"RP Revised Due Date": "",

"RP Completion Date": "43496",

 

Output Where RP Revised Due Date would populate

TomDennison_BDP_0-1612287030391.png

 

Error Where Compose 4 Would be Used

TomDennison_BDP_1-1612287030391.png

 

 

Design Time Use of Compose 4

 

TomDennison_BDP_2-1612287030391.png

 

 

Design Time Expression for “Revised Due Date”

 

TomDennison_BDP_3-1612287030391.png

 

 

 

@TomDennison_BDP  You did not change compose output. it should be compose 4 output.

Hardesh15_0-1612287834975.png

 

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh

 

You are correct. Sorry, been at this a while! Thanks so much, I am good!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (2,025)