Unanswered

Dealing with Blank Values when Moving Records from Excel to SharePoint List with a Flow

(0) Share

Report

Posted on by mr_yogi

Hi,

I've just started using the Power Platform, and I created a simple test cloud flow to copy data from an Excel file on SharePoint to a List. It uses a condition to determine if each record in the spreadsheet is new or to be updated and writes to the List using either Create Item or Update Item.

This worked fine with my test flow with sample data, however now I'm recreating it with real world data there are a lot of blank fields, which are breaking the flow. Fields of type string are fine, but fields set to Numeric and Date/Time in the SharePoint List are causing issue. I have tried using an expression within the Create Item and Update Item fields, but that doesn't work, maybe I have the syntax wrong? Also the columns in the Excel spreadsheet are not formatted, so blank values are set to "General".

I have tried variations of this:

if(equals(Item()?['some numeric field'], ""), 0, Item()?['some numeric field'])

Is it possible to use an expression like this?

I have also read that Conditions can be used to check for the presence of blank values in specific fields but that seems an awfully convoluted to have a different Create Item or Update Item for each missing column that may contain a missing value.

Are there better ways?

20230602-Power Flow Error - Blank Dates.jpg

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Ellis Karim 12,098 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @mr_yogi ,

You can use the empty function to check if string column contains any data or not. Try the following:

if(empty(Item()?['some text field']), "empty", Item()?['some text field'])

See: Reference guide for expression functions - Azure Logic Apps | Microsoft Learn | Empty

For your numeric columns, do you mean that the column does not exist in the data set?

Ellis
____________________________________
If I have answered your question, please mark the post as ☑️ Solved.
If you like my response, please give it a Thumbs Up.
My Blog Site

Was this reply helpful? Yes No
mr_yogi 2 on at

Like (0)

Report
Copy link

Link copied!

Thanks,

I'll give that a go.

Just to clarify regarding the the numeric and date columns: In the excel sheet the columns are not formatted, so are all of type "general", which I guess is a string. Then I assume Excel automatically formats individual cells when either a price, quantity or date/ time is input into one of these columns. However not all records have values for these fields, so many remain blank and are formatted as "general". In the SharePoint List I have set these columns to their like type (currency, numeric, date/time). The problem comes from records with blank values in these currency, numeric and date/time columns, which are formatted as 'general'. The Flow, using "create item" or "update item" attempts to add an empty string ("") into a currency, numeric or date/time column in the SharePoint List.

Hope that makes sense.

Regards

Was this reply helpful? Yes No
sompolk2 5 on at

Like (0)

Report
Copy link

Link copied!

I have the same problem and your suggestions is works. Thanks so much.

Was this reply helpful? Yes No