cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How can I fix this expression?

How would I get this expression to say, 'if value in the field is an empty string, return null'. It currently shows the date 1900-01-01 but I don't want it to, i need it to be a null value. Is that possible because I've tried many different options but can't get it to work? if(equals(coalesce(items('Apply_to_each')?['fields']?['FIELDS.3983'],'null'),''),'',formatdatetime(coalesce(items('Apply_to_each')?['fields']?['FIELDS.3983'], '1900-01-01'),'dd/MM/yyyy'))

8 REPLIES 8
ScottShearer
Super User
Super User

@Anonymous 

It appears as though the value you are looking at comes from Excel - am I correct?  Do you want to update an excel cell with a null value?  Where do want to use the null value?

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

Scott
Anonymous
Not applicable

@ScottShearer it's actually data that's coming from an API and in a JSON format. When I get the data, sometimes it comes through as an empty string but I need to convert it in the body of a POST request so that it says if the value is an empty string, use null. The API I'm sending it to will only allow date format in ISO 8601 or null.

Hi!

" The API I'm sending it to will only allow date format in ISO 8601 or null"

Did you considered injecting your JSON output to the destination API removing the attribute you wanna make null?

removeProperty() can help you to achieve your goal

 

If you share an example of the JSON output, we can suggest a way to proceed

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

@ScottShearer

 

   [
         {
            "Fields.CX.CONTRACT.DATE": "",
             "Fields.MS.START": "1/31/2019 12:00:00 AM",
             "Fields.LOG.MS.DATE.DOCS SIGNING": ""
        }
    ] 

 

But there are multiple objects that come back and they all are different. So this one doesn't have a date in the CONTRACT.DATE field and the MS.START does, but the next object may have a date for CONTRACT.DATE and MS.START may or may not. When I POST the data to the next API I am trying to use an expression that will alter the empty string to just null if it's empty - or remove the whole field if it's null.

 

Hi!
Thanx for sharing a JSON input example!
Working on it!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

@ScottShearer

{
  "data": [
    {
     "Fields.CX.CONTRACT.DATE": if(equals(coalesce(items('Apply_to_each')?['fields']?['FIELDS.3983'],'NULL'),'NULL'),'',formatdatetime(coalesce(items('Apply_to_each')?['fields']?['FIELDS.3983'],'01/01/1901'),'dd/MM/yyyy'))
    }
  ]
}

This is one of the items I am sending over to the other API. I've tried altering the NULL values by removing the single quotes and removing the date that's in there

 

@Anonymous 

In this example I managed to remove property CX.CONTRACT.DATE from object where this attribute was empty.

If you need to apply this technique to N more attributes, you just need to add N more 'Compose has.CX.CONTRACT.DATE' action blocks (adapting each one to the atribute you want to remove) inside the 'Apply to each'.

Flow_removePRoperty.png

RemoveProperty throws an error if property name has special characters like '.', that's why I had to first replace them with '_', and finally undo the changes when adding transformed objects to outputArray

 

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

@efialttes That's a good option, thanks for that, but I have about 60 of these fields that I need to work on. 

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,652)