store null/blank value into a Date column of a SharePoint list using power automate flow.

(1) Share

Report

Posted on by tusharmehta

Hello Everyone,

I am importing date values from the excel table and there is the possibility some dates are empty/null/blank.

I have created flow and it works fine with other fields but when it comes to date it is giving me following error:

InvalidTemplate. Unable to process template language expressions in action 'Update_item' inputs at line '0' and column '0': 'The template language function 'item' must not have any parameters.'.

I have tried following the formulas but am still not able to figure out how to achieve the result.

addDays('1899-12-30',int(items('Apply_to_each')?['DOB']),'yyyy-MM-dd')

if(equals(item()?['DOB'], null), '', formatDateTime(item()?['DOB'],'yyyy-MM-dd'))

if(equals(item('Apply_to_each')?['DOB'], ''), null, addDays('1899-12-30', int(item()?['DOB']), 'mm/dd/yyyy'))

if(empty(replace(item()['DOB']),'"','')),null,addDays('1899-12-30',int(replace(item()['DOB']),'"','')),'MM/dd/yyyy'))

In excel and SharePoint list, the name of the field DOB and it is created with default parameters.

In excel date format is mentioned below:

Steps by Step will be highly appreciated.

Thanks in advance.

Tushar Mehta

I have the same question (0)

All responses (6)

Answers (0)

v-JessicaG-msft on at

Like (0)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Hi @tusharmehta ,

You could check if the excel date column is a blank value. Add empty value in SharePoint field if excel date column is empty.

Best Regards,

Jessica Gu

Was this reply helpful? Yes No
tusharmehta 84 on at

Like (0)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Thanks for the information,

I have a minimum of 4 dates in single records and I believe all those will be difficult to check with individual conditions.

What I have understood from your reply that when we are passing blank value to SharePoint list it is throwing the error correct ?

Was this reply helpful? Yes No
nscates 18 on at

Like (1)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Does anyone have any input on this? I have a similar situation where I'm trying to import an Excel table into a new SharePoint list, but the workflow fails when it hits empty date fields in the table. I tried building a condition into the apply to each node, as pictured above, but I can't seem to get the expression right, the workflow fails to properly identify the blank fields and crashes before completing.

I have considered placing a false 1901 date in all of the fields that do not have one, but that seems like a clumsy workaround - can anybody provide an approach that will allow any blank field in the excel sheet to properly import as a blank/null field in the SharePoint list? Many, many thanks in advance!!!

Was this reply helpful? Yes No
USNHAuto 20 on at

Like (6)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Hello all,
I had a similar situation but I am getting data from power apps and the date input is blank
What I did is this: (in SharePoint Create Item)
if( less ( length ( item()['Date'] ) ,1 ) , null, item()['Date'] )

I hope it helps anyone looking for this in the future!

Was this reply helpful? Yes No
nscates 18 on at

Like (0)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Thanks USNHAuto, that expression did the trick!

Was this reply helpful? Yes No
crisdiglesias 22 on at

Like (0)

Report

Re: store null/blank value into a Date column of a SharePoint list using power automate flow.

Hello @USNHAuto, thank you for your solution.
I would like to make an observation about something that caused me to lose quite a bit of time until I understood what was happening. I have been using the new Power Automate designer routinely due to its aesthetics and fluidity. That said, when applying your solution, my flow consistently displayed the same error message mentioned by @tusharmehta. After several hours of not understanding what was going on, I decided to revert to the old Power Automate designer and test the flow again. Surprisingly, it didn’t show any errors. Once I realized that the initial error was related to the new designer, I returned to it and tested again. To my surprise, the flow completed successfully without the initial error message.
I hope this account of my experience is useful in some way. Regards!

Was this reply helpful? Yes No