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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (1,880)