Answered

Check for NULL value in Formula

(0) Share

Report

Posted on by TomDennison_BDP

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!

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (2)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

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

Was this reply helpful? Yes No
TomDennison_BDP 47 on at

Like (0)

Report
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?

Was this reply helpful? Yes No
Verified answer

Hardesh15 7,087 Super User 2024 Season 1 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
TomDennison_BDP 47 on at

Like (0)

Report

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)

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
TomDennison_BDP 47 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

TomDennison_BDP 47 on at

Like (0)

Report

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

Error Where Compose 4 Would be Used

Design Time Use of Compose 4

Design Time Expression for “Revised Due Date”

Was this reply helpful? Yes No
Hardesh15 7,087 Super User 2024 Season 1 on at

Like (0)

Report

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

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

@Hardesh

Was this reply helpful? Yes No
TomDennison_BDP 47 on at

Like (0)

Report

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

Was this reply helpful? Yes No