cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ryan2k
Level 8

How to filter data from Excel rows

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
Screen Shot 2019-05-06 at 5.12.34 PM.png

 

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:

Screen Shot 2019-05-06 at 5.15.34 PM.pngScreen Shot 2019-05-06 at 5.15.17 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
ryan2k
Level 8

Re: How to filter data from Excel rows

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.

8 REPLIES 8
Community Support Team
Community Support Team

Re: How to filter data from Excel rows

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,

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.
ryan2k
Level 8

Re: How to filter data from Excel rows

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
Level 8

Re: How to filter data from Excel rows

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
Level 8

Re: How to filter data from Excel rows

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
Level 8

Re: How to filter data from Excel rows

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

Screen Shot 2019-05-07 at 9.38.16 AM.png

 

Here's my filter query:

Screen Shot 2019-05-07 at 9.39.09 AM.png

 

 

And here's the header in the Excel Spreadsheet:

Screen Shot 2019-05-07 at 9.39.46 AM.png

ryan2k
Level 8

Re: How to filter data from Excel rows

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
Level 8

Re: How to filter data from Excel rows

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
Level 8

Re: How to filter data from Excel rows

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.

Helpful resources

Announcements
firstImage

Microsoft Flow Online Conference

Join us for a FULL day of FREE Microsoft Flow Sessions from some of the best minds in the industry!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 50 members 4,260 guests
Please welcome our newest community members: