cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Stalerik
Level: Powered On

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

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!

 

flow actions - filter array and delete a row.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

 

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
fchopo
Level 8

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

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
Level: Powered On

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

@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?flow actions - delete a row from filter array.png

Community Support Team
Community Support Team

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

 

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stalerik
Level: Powered On

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

@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
Level: Powered On

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

@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.

Community Support Team
Community Support Team

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

 

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (5,245)