cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tusharmehta
Helper II
Helper II

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

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')

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. 

 

tusharmehta_0-1643287082068.png

 

tusharmehta_1-1643287153628.png

 

In excel date format is mentioned below:

 

tusharmehta_2-1643287275460.png
tusharmehta_3-1643287329306.png

 

Steps by Step will be highly appreciated. 

 

Thanks in advance. 

 

Tushar Mehta

 

 

 

3 REPLIES 3
v-JessicaG-msft
Community Support
Community Support

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.

 

vJessicaGmsft_1-1643620504073.png

 

Best Regards,

Jessica Gu

 

tusharmehta
Helper II
Helper II

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 ?

nscates
New Member

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!!!

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (3,005)