Exclude Total Row from Filtered Excel File

[coolbeans23]

Hi,

I have a flow where it filters an Excel file then copies it to another Excel workbook.  I used "List Rows Present in a Table" action then used the Filter Query input to filter the data to the records I'm interested in. I then use a  "Add a Row into a Table" within a "Apply to Each loop" to add each value for all the fields.  However when it copies the filtered data to the other Excel workbook it also copies the total row of the Excel table.  How do I exclude the total row from being copied? It looks like the Filter Query input only allows a single condition.

Below shows the data copied and for some reason the Total row from the source file also gets copied. I need to have the total row excluded when it copies the filtered data.

[NathanAlvares24 · Accepted solution]

Hi @coolbeans23 !

Just use a "Filter array" action after your "List Rows Present in a Table" action, to filter out the rows with only the word 'Total' from the value from your "List Rows Present in a Table" action. And then add that Body (array) to your Apply to each action as seen below:

Then use the below expressions in your "Add a Row into a Table" action like below:

Expressions used:

Date: items('Apply_to_each')?['Date']
Region: items('Apply_to_each')?['Region']
ID: items('Apply_to_each')?['ID']
Amount: items('Apply_to_each')?['Amount']

Input:

Output:

I hope this helps.

[coolbeans23]

Here is a silly follow up question for understanding - when I used the "Add Row to a Table" action by using the Body from the previous step (i.e. Filter Array action) and then I simply selected the Date, Region, ID, Amount fields from the "List Row Present in a Table" selection.  When I did that it did not show them in expression forms like you mentioned i.e.

items('Apply_to_each')?['Date']

but after I saved all those fields became expressions without me having to type those expressions.  Why does those field selections convert to expressions automatically after I save them?

Thank you in advance.

[NathanAlvares24]

Hi @coolbeans23 !

I am also not completely sure why those don't appear in the dynamic content, could be a bug or 'Filter Array' action is a custom made one where you are filtering using custom formulas or expressions. So it may not recognize it.

Initially what I had done is I built the flow till "Filter Array" action, ran the flow, and then I check the output of the "Filter Array" action. It will look like this:

Then I created the next action, "Add a row into a table" but here I used initially the data from "List Rows Present in a Table" action and not from "Select" action. Like below:

This is just for testing purpose to understand what expression those variables are giving me.

Copy the data from any field:

Paste it in the expression tab:

@{items('Apply_to_each_2')?['Date']}

Now you can see how the expression looks like when it comes into an apply to each action. This will mostly be the same for when you take the body of the "Filter array" action. Now, remove all those fields:

Add the body of the "Filter array" action into the apply to each action. And then now for the date, enter into the expression tab the same expression you copied before. Remove the highlighted text i.e. starting remove "@{" and at the end "}", then click OK to save the expression into the Date field:

Initially when you save it, it will appear as an expression. But after running it and then editing it again, it will appear like a dynamic content from the "Filter array" action.

Before running:

After running and go to edit:

This is how I understand the expressions and then I can create my own expressions if its unavailable from the dynamic content.

I hope this helps.

[coolbeans23]

I see. So you copy and paste the dynamic content like Date, Region etc to the expressions to show the formulas.  The way I did it is copy and paste the dynamic content then click Save and it'll automatically convert it to expressions.  Strange behavior but looks like both methods work.  Thanks again.