How to filter data from Excel rows

[ryan2k]

I'm having a tough time understanding the best way to filter Excel data. It seems like I need to use Array Filters, but I can't get them to work the way I need.

I have a spreadsheet that contains internal emails, customer type, and customer emails

Except the real file has thousands of rows. I need to be able to filter the spreadsheet by the Manager Email. Then from the filtered data, send Email_Template_1 to the Technician's emails, and send Email_Template_2 to the Field emails.

Before even getting to the condition, I'm having trouble filtering the data: when I try to filter the data by the manager email (which should match who is pushing the button trigger) the array is empty:

[v-bacao-msft]

Hi @ryan2k ,

The premise of using List rows present in a table action is that the data is stored in an Excel table.

I noticed that field name in Excel is Manager Email, but the parameters you configured in Filter array start with "Engagement:".

Flow run history shows that INPUTS of Filter array seems a bit strange.

Please provide more details and we would provide proper workaround for you.

Best Regards,

[ryan2k]

Hello,

Thanks for the reply @v-bacao-msft. The screenshot of the Excel table was just an example as my actual Excel sheet has sensitive data.

The data is indeed in a Table.

Maybe array filtering isn't what I need? I just need emails to get sent based on the following criteria:

1. The customer needs to have ME listed as the manager

2. If the customer is Field, send Email_a. If the customer is Technician, send Email_b

3. Ignore all other customers that aren't mine

This seems simple enough, but I haven't been able to get it working as expected. In an Excel sheet of 1000+ rows, if I include logic for just matching my email with the Manager column, Flow seems to only check 256 rows, which is why I think I need to filter the data based on the Manager column.

[ryan2k]

Perhaps I can use the ODATA Filter Query instead of Array Filtering. I just need to know how to reference an Excel Column in the formula.

[ryan2k]

Ok testing using ODATA filter queries... how do I reference an Excel Column field that has spaces in it? I keep getting syntax errors on that part.

[ryan2k]

Here's the error I'm getting when trying odata filter query:

Here's my filter query:

And here's the header in the Excel Spreadsheet:

[ryan2k]

Ok it sees the column properly with the spaces in it - I think the issue has to do with the 'eq' - the column header obviously isn't going to equal the user email. Need to find the right filter command

[ryan2k]

Here's my error when trying to filter using odata filter query. I've tried this every which way to Sunday.

{

"status": 400,

"message": "Syntax error at position 21 in 'Engagement_x003a_ TAM Manager Email eq 'me@email.com''.\r\n inner exception: Syntax error at position 21 in 'Engagement_x003a_ TAM Manager Email eq 'me@email.com''.\r\nclientRequestId: 0e8413e4-0165-4be8-b4e0-bc53eac8b780",

"source": "excelonline-eus2.azconn-eus2.p.azurewebsites.net"

}

[ryan2k · Accepted solution]

I worked around this by renaming the excel columns. The original column name looked like

word: more words like this

So I changed it to look like

word_more_words_like_this

and everything is working as expected.

[Anonymous]

@ryan2k - How do you handle instances of a shared Excel file where column names cannot be renamed to accommodate Flow? How do you handle a space in Excel column name with ODATA Filtering in Flow? 

[ryan2k]

Unfortunately have no idea. This specific Flow was for a unique use case where I had the ability to rename the columns as needed.