Streamlining Data Import: Excel to SharePoint List with Power Automate

The client has over 5000K records in an Excel file for fiscal year targets needing upload to a SharePoint List used in Power Apps and Power BI. Data accuracy is crucial. A Power Automate flow with manual trigger, pagination, and termination steps ensures accurate data transfer.

1. Business Case: The client has extensive data (over 5000K records) in an Excel file for upcoming fiscal year targets.
2. Challenge: This data needs to be uploaded to an existing SharePoint List, which is also used in Power Apps and Power BI as Data source. Quick edit view doesn’t work due to the large dataset.
3. Priority: Ensuring data accuracy is crucial.

Solution

Step 1. Create a manual trigger

Create a manual trigger a flow in power automate add below action “List rows present in a table”

Step 2. Add compose action

Add a below expression as Inputs.

length(outputs('List_rows_present_in_a_table')?['body/value'])

Step 3. Create Item action

Before executing flow

Navigate to List rows present in a table setting, enable pagination, and set the threshold value to 100000.

Test the flow

1. Add a Terminate Action
   • Insert a “Terminate” action before the “Apply to each” action in your Power Automate flow.
   • Configure the “Cancelled” action with an appropriate message (e.g., “Testing flow execution”).
2. Run the Flow
   • Execute the flow.
   • Check if the “Compose” action’s input count matches the number of rows in your Excel file.

If your count is matching, then you’re ready to go with the flow by deleting the terminate action.

Thanks for Reading the Blog.

Happy Coding...!