Unanswered

How to fix a flow that fails due to blank rows in a table

(0) Share

Report

Posted on by Krickner

Hi 😊

I'm having a heck of a time developing a working flow that will create a table, then list rows present in a table, and use a condition to determine if rows are new entries or simply entries that need to be updated. I'm getting stuck at the "Condition" step because Power Automate gets to blank rows and fails. I've tried using the Filter Query only to pull rows that have data, but it's not working.

See my flow below: (My issues start with Step 12)

I only have 10 rows of data so items 1 through 9 succeeded, while 10 through my table limit 100 fail.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

David_MA 12,966 Super User 2025 Season 2 on at

Like (0)

Report

I was having a similar problem that I couldn't figure out for months until yesterday. I took advantage of the failure. It is hard to tell what your are doing as the error messages are blocking your actions, so I will explain what I am doing and maybe you can adapt it.

I have a SharePoint list that acts like a distribution list for several workflows. In several forms, there is a lookup to this and the requester chooses the group that they want to notify. However, sometimes the list doesn't get updated and people who have left the company are still in the list. I needed to create a flow to check if each person is still active. I use an apply to each to get the user profile for each person in the people field:
When there is an invalid person, the Get user profile action fails just like yours does for the blank items. Below your condition add two parallel actions and configure one to run on failure and one to run on success. For my solution, I am appending two array variables of valid emails and invalid emails. The valid ones run on success and the invalid ones run on failure.
Then in your first action below you apply to each, set that to run on both success and failure:
You need to do this because your condition is in the apply to each and therefore the apply to each will be considered fail as well.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
It looks like the issue is that some of your fields in SharePoint are set to certain data types, such as Priority (as per your last error message) being type Number so expects a number.

To get around this, you can use coalesce which allows you to pass in a number of parameters, and it will return the first one that is NOT null/empty. If your Priority field value is null/empty, then it will move onto the next parameter and use that if it's not null/empty, and so on. This could be a default value such as 0 (or whatever you would want for a default value) for your Priority field. See expressions below.

//Updating the Priority field //Using coalesce, if items('Apply_to_each')?['Priority'] is null/empty, then it will default to 0 coalesce(items('Apply_to_each')?['Priority'], 0) //If your field in your SharePoint list is a text field, then you could just use the expression below (including the ?) that would just return null/empty items('Apply_to_each')?['JobNotes']

Alternatively, if you knew it was a Number data type then you could use the new isFloat expression.
//Checks if the value is a valid float (number). If yes, then it uses that value, otherwise null if(isFloat(items('Apply_to_each')?['Priority']), items('Apply_to_each')?['Priority'], null)

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

@grantjenkins your feedback is good, but I don't think I understand as I should. The issue I'm running into with the condition appears to be the result of blank rows in a table, as those are the only items failing. The reason I have blank rows is when using the "Create table" piece of the flow I cannot reference relative table size, I must use a number of rows (like 2000) as I know my data set will never be over 2000 rows.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

@David_MA I think you're on to something here. The only thing I'm not understanding is my issue seems to be tied to blank rows, and I do not need to combine data sets. Perhaps I'm missing the mark here? Are you stating that I can create a condition to run for all of the blank rows, and a condition to run for all of the rows that have data?

Was this reply helpful? Yes No
David_MA 12,966 Super User 2025 Season 2 on at

Like (1)

Report

It's not really a condition, it is how you configure the run after settings for the step that comes after the failure. If the failure is acceptable because you know what is causing it (in my example doing a get user profile on an account that has been deactivated), then you can set the action after it to run whether the prior action has success or has failed.

I just set up another flow today to take advantage of failures because I know why it is failing and that is okay. In my process I have a flow that runs every day on a list to find expiration dates that will expire in four weeks. It sends an e-mail to the manager listed in the item asking if the expiration date needs to be extended.

However, there were problems with this as there were a number of requests where the manager is no longer with the company, so nobody was getting notified to ask if an extension was needed, which resulted in another process removing access.

So, I created a flow to run each day on this list to check the manager listed and see if the account is still active. If not, it sends an e-mail to HR so they can see who replaced the person so that the item in the list can be updated. This is the flow:
It is manually run for now as I am still testing, but plan to switch it to a scheduled flow. The three variables are all integers and I use them to count valid managers, invalid managers and the total number of requests it reviewed. If it finds items (the get items action is filtered to active items), there is an apply to each action to process each item:
Below the Get user profile (V2) action I have two parallel actions. The one on the left is to configured to only run on success while the one on the right is configured to run on failure or skipped:
As you can see, the failed side sends an e-mail so that the item can be updated with a valid manager. Outside of the Apply to each I have an e-mail action that sends the results of the run. This is configured to run on both success and fail since if there is a failure in the Get user profile action, it also counts as a failure in the Apply to each.

I hope this makes sense.

David

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report
@Krickner That makes sense, and easy fix.

All you actually need to do is add a Filter Query within your List rows present in a table so it excludes any rows where the JobNumber is empty (assuming the JobNumber should always be filled in). This would ensure only rows that contain data would be returned.
//Note that this is using two single quotes JobNumber ne ''

Also, just to note - you said that you could have up to 2000 rows of data. By default, List rows present in a table will only return the first 256 rows (maximum). If you were expecting more than this, then you would need to go into Settings, turn on Pagination, and set a Threshold larger than the number of rows you were expecting. In your case, I'd suggest setting the Threshold to 2000 since you never expect more than this.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

@grantjenkins that worked very well. Thank you for the additional guidance on pagination and threshold limits. : )

Was this reply helpful? Yes No