Upload from Excel Skip Empty

[cmitchener]

I have been trying to construct a flow to be able to grab the data from an online Excel file and upload it into my SharePoint list. Everything is working fine, but since the Excel table has a lot of rows, the flow continues running the Apply for Each until it gets to the end (which takes more than 10 minutes). I would like the flow to stop after finding a certain number of empty rows. How would I go about doing this (screenshots below for reference)?

[Kabilesh]

Hi @cmitchener 

Can you please mention whether the empty row count is constant for all flows or its dynamic/

Best Regards,

VK

[cmitchener]

It should be a constant; so I do know that the flow executes this for every record, making sure the record isn't empty. If it is, it doesn't upload it into the SharePoint list and goes on to check the next one. The issue is I want the flow to detect if this happens 3 times, the flow terminates. I tried to achieve this with a variable (which I have since removed), but Terminate cannot be used within an Apply to Each.

[Kabilesh]

@cmitchener So after finding three empty rows, then it should terminate the flow. Am i right?

[cmitchener]

Correct

[grantjenkins · Accepted solution]

@cmitchener Is there a particular reason why you want to stop the flow after finding three empty rows?

There is a much more efficient way to construct this flow so it's only iterating over items that match your two conditions. I mean we can filter out any rows based on your conditions prior to the Apply to each.

For this example, I have the following Excel Table. We only want to return items where MemberNumber is not EMPTY and AuditComment is not SUPERVISOR INCENTIVE.

The full flow is below. I'll go into each of the actions.

List rows present in a table retrieves the rows from our Excel Table. It allows us to use a single condition within Filter Query. In this instance I've used the following condition below. This will help speed up the retrieval of items since we're not returning all items now.

AuditComment ne 'SUPERVISOR INCENTIVE'

Filter array then takes in the output from List rows present in a table to apply the second condition. This will result in just the items we want to use.

Apply to each will iterate over each of the items from Filter array (just the items we want as they have already been filtered). Within the loop we use Create item passing in the values we want. Note that we don't have any conditions within the loop as the items have already been filtered.

To get our Excel values from the current item for use in our Create item we can use the following expressions.

//MemberNumber
items('Apply_to_each')?['MemberNumber']

//AuditComment
items('Apply_to_each')?['AuditComment']

//To get other fields you just need to specify the actual column header
items('Apply_to_each')?['COLUMN_NAME']

[Kabilesh]

Hi @cmitchener 

Hope this might help you,

Best Regards

VK

[cmitchener]

Thank you, this seems to work perfectly! The reason I wanted to stop the flow after a few blank records is because the code I had continued to evaluate every row in the sheet after the data stopped, but this code is doing that just fine. Thanks again!