Excel Online (Business) - Delete a Row from Excel Table if Value in Column is blank

[Stalerik]

Hello,

I am trying to look at a named Excel table in a file on Sharepoint and delete all of the rows where the first column is blank.  I am using the following actions:  List rows present in a table; Filter Array; Apply to each and delete a row.  I am not sure how to get this sequence of actions to find all the rows where "Account" column is blank and delete that row from the Excel table.  I think I am not understanding what Key Column and Key Value mean?  This is what I have, and right now it is deleting no rows.  Thank you!

[fchopo]

Hi @Stalerik 

Have a look at this thread with a similar issue: https://powerusers.microsoft.com/t5/Building-Flows/Delete-Rows-w-Excel-Business-connector/td-p/20092...

Apart from that, I would check if the Account field is empty using null expression. 

Hope it helps.

Ferran

[Stalerik]

@fchopo 

Thank you for your reply.  I have reviewed the thread.  I've found it confusing since there are so many messages in it but I have made a little more progress.  The Filter Array appears to be working correctly.

As configured in the attached image, I was able to see the output of the filter array to be correct.  I am using not(empty(item()>['Account'])) as the value in the left field -- to find if the value is not empty (= true).  When I use the condition "is equal to", the flow will create a Filter Array of only the non-blank rows in my table, and then move on to delete all of the non-blank rows, leaving only the blank rows.

However assuming that I want the blank rows removed instead, I change the condition to "is not equal to" and then the Filter Array find all the blank rows, but the delete rows gives an Status Code 404 error with a Message of "Resource Not Found".  Do you know why this is happening and how to delete the rows where "Account" is blank?

[v-bacao-msft]

Hi @Stalerik ,

Flow currently does not support using Dynamic content / expression / manual input to configure File and Table fields. We can only select File from File browse and select a table from the drop-down list.

The configuration in your screenshot is not supported, please check this link and get more details about known-issues-and-limitations:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#known-issues-and-limitations

Best Regards,

[Stalerik]

@v-bacao-msft 

The delete a row action seems to work correctly when using not(empty(item()>['Account'])) is equal to 'true' as it deletes all of the non-blank rows from the Excel table.  However it does not work when using not(empty(item()>['Account'])) is not equal to 'true' to delete all of the blank rows.  Can you please explain why this is different?  The dynamic references are the same for these two conditions.  Thank you.

[Stalerik]

@v-bacao-msft 

I have also looked at the "known issues and limitations" and I see the following information:

The File property expected by the connector's actions should be filled using one of the following options:

• Pick a file from the file picker.
• Use an output from the OneDrive for Business connector's triggers/actions (file's Id or File identifier property depending on which one is present for the particular OneDrive for Business's action or trigger).
• Use an output from the SharePoint connector's triggers/actions (file's Id or Identifier property depending on which one is present for the particular Sharepoint's action or trigger).

So it sounds like I can do more than "only select File from File browse".  I am using the SharePoint file ID as the filename so I assume that it should work based on the information above?  Can you please clarify your response if I am misunderstanding?  Thank you.

[v-bacao-msft · Accepted solution]

Hi @Stalerik ,

Sorry I didn't notice "Use an output from the SharePoint connector's triggers / actions (file's Id or Identifier property depending on which one is present for the particular Sharepoint's action or trigger).". thanks for your reminder.

From the error you provided, this seems to be the expected result. If Account is selected as the key column, it needs to be a non-null field.

Null cannot be used as a field value to delete the specified rows.

You can add a new field, set the value to true/false, if Account is empty, then set the field value to false, and finally use this field as the key column to delete rows.

Best Regards,