Answered

Expression to pass blank and text value from Excel to Sharepoint

(0) Share

Report

Posted on by third_learner

115

Hi. Asking for your help on how to modify the expression below. I have a date field in Excel that has a default text value but the parallel column of that field in SharePoint is a date data type column. I want my flow to pass if the date field is blank or did not change the default text value and returns blank in SharePoint. In short, whether the field is blank or did not change the default text value, the flow will return blank if no date was selected in the dropdown of the field in Excel and avoid the error below in Create item action. The column in SharePoint is set as date data type.

Here's the current expression:

if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'),

null, items('Apply_to_each_2')?['Resources Assigned Date'])

Thank you very much for your help.

Categories:

Building flows

I have the same question (0)

All responses (18)

Answers (1)

Sort by

Verified answer

v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @third_learner ,

Is 'Resources Assigned Date' a date column in excel table?
Please check if ISO8601 has been turned on in "List rows in a table" action and modify your expression as follows:
if(contains(items('Apply_to_each_2')?['Resources Assigned Date'], '#VALUE!'), ' ', items('Apply_to_each_2')?['Resources Assigned Date'])

Best regards,
Rimmon

Was this reply helpful? Yes No
third_learner 115 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

Yes, the 'Resources Assigned Date' is set as a date column in Excel. ISO 8601 has been turned on also.

I tried your expression above but I got the same error

This what it looks like in Excel.

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Please try this:
if(contains(string(items('Apply_to_each_2')?['Resources Assigned Date']), '#VALUE!'),null,items('Apply_to_each_2')?['Resources Assigned Date'])

Was this reply helpful? Yes No
third_learner 115 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft I used your revised expression above but still got the same error. I just wonder if my column in SharePoint list is set to Date data type, does it accept a text value as an alternative if no date provided in the Excel?

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @third_learner ,

First error: The value is not in the string/date format.
Please check whether the column type is Date type.
My test column:
Outputs:

Second error: The null value cannot be recognized, check whether there is a null value in this column, if so, you need to filter out the rows whose value is not null in this column.

Was this reply helpful? Yes No
third_learner 115 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft

I confirm that the field in Excel is formatted as Date data type. I have this in my Excel column. If there is no selected month and year or if the field is blank from another sheet, the value is #VALUE!

But my flow keeps failing in the Create item action using your expression.

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Sorry for delay reply.
I recommend you check outputs of this column as well. You can show outputs to me if you don't know how to deal with it.

Was this reply helpful? Yes No
third_learner 115 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-mengmli-msft
This is the output in Compose if the the Date column in Excel is blank.

Was this reply helpful? Yes No
v-mengmli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

How did you get #VALUE!? Manually or Formula?
If you are using Formula, what is it?

Was this reply helpful? Yes No
third_learner 115 on at

Like (0)

Report
Copy link

Link copied!

@v-mengmli-msft
I get the #VALUE! because I have a formula in Excel. See below.

Was this reply helpful? Yes No