cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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
Highlighted
Resolver I
Resolver I

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.

View solution in original post

10 REPLIES 10
Highlighted
Community Support
Community Support

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.
Highlighted
Resolver I
Resolver I

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.

Highlighted
Resolver I
Resolver I

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.

Highlighted
Resolver I
Resolver I

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.

Highlighted
Resolver I
Resolver I

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

Highlighted
Resolver I
Resolver I

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

Highlighted
Resolver I
Resolver I

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"
}
Highlighted
Resolver I
Resolver I

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.

View solution in original post

Highlighted
Post Patron
Post Patron

Re: How to filter data from Excel rows

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

Highlighted
Resolver I
Resolver I

Re: How to filter data from Excel rows

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (6,693)