Issues using Power Automate to copy from Excel to Sharepoint List

[AndrewMG]

Hello!

Let me start by saying I have been reading through similar problems and I have tried out:

- if(equals(x,''),null,x)

- if(empty(x),null)

- DateTime Format is set to ISO 8601

My flow takes data from Excel (table) and adds it to a SharePoint List.  I am running into two issues:

1. My blank date fields throw an error

2. My flow creates 4x of the records

1. I've seen this all over the place but the error it gives me is: 

"The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/field_23' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'."

My function currently has: If(empty(item()?['Award Date']),null,item()?['Award Date'])

where 'Award Date' is the Dynamic content field from Excel.

2. I'm not sure what I messed up in my flow, but each record is getting added 4 times. I will unclude screenshots.

---------- Overview of Flow ----------

---------- Apply to each ----------

 

---------- List Rows ----------

---------- Apply to each ----------

---------- Create Item ----------

 

---------- Error ----------

 

[creativeopinion]

@AndrewMG The logic of your flow needs to be adjusted. The issue is your nested Apply to Each actions.

Your Current Flow Logic

The first Apply to Each action is looping through the results returned from the Get Files (properties only action). In your screenshot it shows that 4 files have returned.

Each time you are looping through a file, you a running a List Rows present in a table action. This is incorrect. You should always run List Rows present, Get Items, Get Files etc actions OUTSIDE of an Apply to Each action. You should only run these actions ONCE in a flow and use a Filter Array action instead. This will make your flow more efficient and avoid unnecessarily running those actions. Run once, then filter the results in subsequent actions.

To learn more about how to use the Filter Array action, please refer to this YT Tutorial I uploaded.

While you are looping through a file, you are then running another Apply to Each action to loop through each item returned from the List Rows Present in a table action. This logic will not get return the results you are expecting.

Can you provide more insight as to what the purpose of the Get Files (properties only action) in your flow is for? You only mentioned that you want to take the data from your Excel table and copy it to your SP List.

You can refer to this YT Tutorial: ✌️TWO WAYS to Get Data from Excel into a SharePoint List

Where I cover the following:

✅ How to copy and paste data from Excel into a SharePoint List

✅ How to format Excel data for multi-choice columns in a SharePoint List

✅ How to import data from Excel into a SharePoint List with a Power Automate Flow

✅ How to use the Select action in Power Automate

✅ How to use the Create Item action in Power Automate

✅ Enabling the Concurrency Control to Optimize Flow Performance

This section is where I cover how to create a flow to get your Excel Data into SharePoint. However, keep in mind this is a simple flow that gets your data from Excel to SP. 

If you require a more complex flow that compares your Excel Data to the data that already in Excel—you'll need to reference this YT Tutorial: Are you using the Microsoft Power Automate Filter Action wrong?

In this tutorial I cover:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

This section is where I cover how to cross-reference data between an Excel table and a SharePoint list.

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok