Unanswered

Handling multiple data formats in a Sharepoint List DateTime field.

(0) Share

Report

Posted on by joshua-wolff

I'm using power apps to bulk upload excel files to sharepoint. I have a flow that triggers when these files are modified or created and creates or updates list items based on some key/value pairs available.

The flow works fine until I hit some bad data in my excel files. Specifically DATE fields. They vary from null, excel serial dates, and dd-MMM-yyyy. For documents without excel serial dates I simply use:

if(equals(items('Apply_to_each_10')?['PROJ QTR1'],''),null,items('Apply_to_each_10')?['PROJ QTR1']) - for example

This handles the null values without issue but once Excel Serial dates are erroneously introduced, I'm at a loss. I've attempted multiple different variations, used null-coalescing operators to get around nested if statements...I'm stumped. Below are some examples of my attempts following power automates documentation rules:

Attempt 1 // cannot get 'isNumeric' to hit.
if(equals(items('Apply_to_each_6')?['DEPARTURE_DATE'],''),null,IsNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE']),formatDateTime(addDays('12-30-1899',int(items('Apply_to_each_6)?['DEPARTURE_DATE'])),'dd-MMM-yyyy'), items('Apply_to_each_6')?['DEPARTURE_DATE'])

Standalone // works but null is still an issue
IsNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE'])) {formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE'])), 'dd-MMM-yyyy')

Attempt 2 //else if doesn't function that way...no go
if(equals(items('Apply_to_each_6')?['DEPARTURE_DATE'],''),null,items('Apply_to_each_6')?['DEPARTURE_DATE']),else if(isNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE']),formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE']),'dd-MMM-yyyy'),items('Apply_to_each_6')?['DEPARTURE_DATE'])

Attempt 3 // swapping equals and forcing a reformat...testing. Script VALID from power automate, Woohoo!
if(isempty(items('Apply_to_each_6')?['DEPARTURE_DATE']),null,formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE']),'dd-MMM-yyyy'),items('Apply_to_each_6')?['DEPARTURE_DATE']))

// Attempt 3 is throwing an error because forcing dd-MMM-yyyy through the int() function isn't possible. I need find nested if statement workaround.

Attempt 4 // nested if's // nested if's are not allowed in powerfx...fml.
if(isempty(items('Apply_to_each_6')?['DEPARTURE_DATE']),null,if(isNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE']),
formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE'])),'dd-MMM-yyyy'),items('Apply_to_each_6')?['DEPARTURE_DATE'])))

Attempt 5 // last chance today... // the '?' prior to formatDateTime is a null-coalescing operator...we may be onto something...
if(equals(items('Apply_to_each_6')?['DEPARTURE_DATE'],''),null,isNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE']) ? formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE'])),'dd-MMM-yyyy') : items('Apply_to_each_6')?['DEPARTURE_DATE']))

Attempt 6 // Lets null-coalesce them ALL !!!
return isempty(items('Apply_to_each_6')?['DEPARTURE_DATE'])?null : isNumeric(items('Apply_to_each_6')?['DEPARTURE_DATE']) ? formatDateTime(addDays('12-30-1899', int(items('Apply_to_each_6')?['DEPARTURE_DATE'])),'dd-MMM-yyyy') : items('Apply_to_each_6')?['DEPARTURE_DATE'])

// isempty doesn't work because it's stopping at the first null value and attempting to null it. Script invalid.

As you can see, I was also all over the place with syntax trying various methods...HELP

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

I'm a big fan of coalesce and suggest you give it another try. Throw away the isEmpty part though, it is counter productive.

Power Automate Coalesce Function Explained (zeitgeistcode.com)

Depending on which formats you expect most I would first try to convert from regular date (and replace with null if it fails) then try to convert from numerical date (and replace with null if it fails) and then provide the fallback value (current date or null etc).

Was this reply helpful? Yes No
joshua-wolff 12 on at

Like (0)

Report

I expect correct dates 99% percent of the time with null values as the next 'most likely' input. The excel serial date is clearly a mistake on the data entry side. My challenge is flow longevity. I need this to function with minimal data scrubbing. I'm lacking in data literate and/or power savvy people in my organization. The key is simplicity of use. I intending on using the resulting script in ALL date fields regardless of how rare the issue is. Thank you for your comment and link. I'm hopeful someone in the community has dealt with this issue previously and provide a variation of the expression I can bastardize. I hope you have a good week!

Was this reply helpful? Yes No
joshua-wolff 12 on at

Like (0)

Report

It looks like coalesce can only take a singular data type so it's not the answer I'm looking for per:
https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-isblank-isempty
'All arguments to Coalesce must be of the same type; for example, you can't mix numbers with text strings. The return value from Coalesce is of this common type.'
My issues is numeric values e.g. excel serial date, date type, and null values. Null obviously isn't the issue here, but the numeric conversion requires a boolean return and I'm not sure how to package that up.

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

I see that I wasn't clear in my statement. I was proposing to use Coalesce after you had converted the results to datetime format (or null, in case of a failed conversion). Coalesce would then simply collect the first non-failed conversion.

Would you mind providing some sample data that would cover your scenarios?

Was this reply helpful? Yes No
joshua-wolff 12 on at

Like (0)

Report

Understood. I'll try creating a global variable, set that variable after my 'list rows present in table' action to handle the numeric value conversion to date, then handle the correct date formats and null values in the actual field.

I've uploaded a sample dataset if you're willing to take a stab at it. The follow-on to this would be, how do I handle that for ALL date fields in my flow? I have numerous date fields. Can they all be handled by pointing back to a generic 'DateField' variable or will I need to create multiple variables?

DatesBadDatesAndN...

Was this reply helpful? Yes No
lbendlin 8,474 Super User 2025 Season 2 on at

Like (0)

Report

You will have to implement the logic for each column separately, but you can likely combine that work in a single Select statement.

Please provide sample data that fully covers your scenario. Please indicate the expected outcome based on that sample data.

Was this reply helpful? Yes No
joshua-wolff 12 on at

Like (0)

Report
In the table provided above, the DEPARTURE_DATE column has empty strings (in power automate this means null but not null), excel serial dates (just one), and dd-MMM-yyyy data. I need all data to be dd-MMM-yyyy or null.

The sensitivity of my data doesn't allow me to share the full flow with data fields but here is an overview image.
I have a power app that allows members to bulk upload specific files. These files maintain their name and overwrite existing files on sharepoint.
The flow triggers on sharepoint file create/modified (properties only)
In the settings, I've turned 'split' off so I receive an array (JSON)
I parse the JSON and extract the file names and branch them into file-specific actions.
The flow is just updating sharepoint lists to feed additional powerapps.
Get tables (excel), list rows (excel), get items from sharepoint
Create item (if new)
Update item (if old)
the variable and compose actions are my current efforts to make it work. Every other branch doing the same thing works great. Its just one branch failing because of this data type issue. Thanks again for your support.

As I said before, I have no one in my organization that can/will maintain this and I want to mitigate any potential failures. I also like to learn by trying things a bit beyond the documentation : ) Thanks again for your help!

Was this reply helpful? Yes No