How to ignore a split result if the value is null?

[FaroukM]

I am trying to create a flow, where I am converting the EU date format in an excel sheet into US format and then adding it to a form

1. Flow gets the value from an excel sheet in SharePoint
2. The format is dd/MM/YYYY in string
3. The flow gets the value in the "Get Date from Excel"  e.g. 13/12/2022
4. The split will get me "13", "12", "2022"
5. I then concat and recreate the date in US format 12/13/2022

Hwever if the excel "date" cell is empty then the split crashes as it requires a "if(empty)" expression but if I use "null" then the flow isn´t very happy with me...

The split expression currently is:

split(outputs('Get_Date_from_Excel'), '/')

The concat expression currently is:

concat(outputs('Split_Date_without_time')[1], '-', outputs('Split_Date_without_time')[0], '-', outputs('Split_Date_without_time')[2])

My end game is to allow the flow to continue if the value is null (empty) and then create/update a form without crashing at the start as the cell in excel is empty (no value)

Thank you for any help

[FLMike]

Hello,

let me ask this. If one of those values is null should the flow stop (not continue?) if so

Then you should put a condition around everything under Get Payment Data From Excel

And the condition would be an Or condition where if either is NULL or not data do nothing or log a warning or something.

let me know if you still want to process though

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Chriddle]

I recommend to not use split() but use parseDateTime() for this:

Additionally coalesce helps to handle null:

if(
	equals(
		coalesce(item(), ''),
		''
	),
	'',
	formatDateTime(
		parseDateTime(item(), 'es-es', 'dd/MM/yyyy'),
		'MM/dd/yyyy'
	)
)

returns

[
  "12/13/2022",
  "",
  ""
]

[FLMike]

Oh and to answer your question directly, in your concat, you would need to wrap each internal area with an coalesce to provide a default value of an if statement, but I am not sure what you would get out of it if they are all NULL or some area, if that is beneficial to you.

concat(
coalesce(outputs('Split_Date_without_time')[1], 'default value'),
 '-', 
coalesce(outputs('Split_Date_without_time')[0], 'default value'),
 '-', 
coalesce(outputs('Split_Date_without_time')[2], 'default value')
)

And in your splits you could do the same, although again not sure the value since its not a date

split(coalesce(outputs('Get_Date_from_Excel'),'default value'), '/')

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[wskinnermctc]

First, I would recommend using variables to hold these date values. All of the compose outputs can look similar and get confusing. In my example I have put the date string into a variable.

I think you can do all of this in a single expression. It looks scary, but it will reduce steps and allow you to check for an empty value.

Here is the full expression:

if(empty(variables('PaymentDate')),'',join(createArray(first(skip(split(variables('PaymentDate'),'/'),1)), first(split(variables('PaymentDate'),'/')), last(split(variables('PaymentDate'),'/'))),'/'))

 Here is the example in a flow:

Convert Date String FlowResults Convert Date String

This long single expression is basically the combination of 3 smaller expressions. I use the join() and createArray() expression basically how you used concat().

Expression to get value from string "dd/MM/yyyy" of MM:

first(skip(split(variables('PaymentDate'),'/'),1))

Expression 2 to get value from string "dd/MM/yyyy" of dd:

first(split(variables('PaymentDate'),'/'))

Expression 3 to get value from string "dd/MM/yyyy" of yyyy:

last(split(variables('PaymentDate'),'/'))

If you want to put the string into the correct datetime format to be used in other parts of the flow you can use the expression below. It is not much different than the original example, except the smaller expressions are reordered.

To convert string "dd/MM/yyyy" to the date format "yyyy-MM-dd":

join(createArray(last(split(variables('PaymentDate'),'/')),
first(skip(split(variables('PaymentDate'),'/'),1)),
first(split(variables('PaymentDate'),'/'))),'-')

Hope this helps!

[FLMike]

Only issue I see is empty and NULL aren't the same for a date, so add a check for empty or Null

[wskinnermctc]

@FLMike are you saying that the value coming from Excel is null? I don't think it would pass a value of null from Excel to Power Automate. Unless it is a literal text string 'null' that is in the excel cell?

But even if it did pass through as null, by putting the Excel value into the variable, the variable would be empty and the expression would still work.

If it is a text string 'null' then OP just needs to stop putting that in the excel sheet.

[FLMike]

I am saying in your code check

if(empty(variables('PaymentDate'))

if it was null that wouldn't catch it, but yes its just standard to validate both,  but agree in the Excel side it should not pass that.

[FaroukM]

If the value is empty (from the excel) then the flow should proceed on and at the end update other values from other cells but leaves the date field empty and save.

[FaroukM]

The business scenario is that that the "dates" in the excel are string and when the client uploads some data then the "Payment Date" might not still be known so they leave the cell blank. Thus I am passing a text that is in the shape of a date to the Flow that turns the EU format of dd/MM/yyyy to MM/dd/yyyy and in the instances, where there is no value (nothing in the excel sheet), then I want the flow to continue on and not crash and save the form with the date field empty