cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveD
Regular Visitor

OData Filtering on Excel Column with Spaces in Header

I have successfully connected a flow to an Excel file that resides on SharePoint. 

 

Many of the table header names in the Excel file use two words, like "Product Name" or "Invoice Date".

 

When I attempt to use the OData filtering or ordering in the MS Flow Get Invoice Items app edit screen I can't seem to get the query right. 

 

For Order By I've tried:

  • Invoice Date desc
  • 'Invoice Date' desc
  • "Invoice Date" desc
  • Invoice%20Date desc
  • [Invoice Date] desc

All of which cause the flow to fail. I'd prefer to not have to change the source Excel table headers as MANY other systems depend on the stability of that file.

 

Can anyone tell me how to solve this conundrum?

 

GetInvoiceItems.png 

 

Many thanks,

- Steven

1 ACCEPTED SOLUTION

Accepted Solutions
RezaDorrani
Dual Super User
Dual Super User

Hi @SteveD 

 

Excel odata query / sort will not handle spaces 

Your best bet will be to update the excel file and remove the spaces

 

Regards,

Reza Dorrani

 

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

3 REPLIES 3
ScottShearer
Super User
Super User

@SteveD 

You might try running the "Get Invoice Items" action without the Order By expression.  After the run, take a look at the output of this action and see how  Invoive Date is referred to in the output.  Use that same syntax in the Order By expression.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
RezaDorrani
Dual Super User
Dual Super User

Hi @SteveD 

 

Excel odata query / sort will not handle spaces 

Your best bet will be to update the excel file and remove the spaces

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

v-bacao-msft
Community Support
Community Support

 

Hi @SteveD ,

 

Unfortunately, Excel-Filter Query/Order by does not currently support fields with spaces in the name.

If you want similar features to be supported by MS Flow, please consider voting for similar idea below:

https://powerusers.microsoft.com/t5/Flow-Ideas/Have-Consistency-in-on-Filter-Query-and-Order-By-on-E...

 

As an alternative to Filter Query, please consider using Filter array to filter rows.

After using Filter array to filter rows, if you want to reference field values, you may need to use the expression.

You could consider parsing the Body of Filter array using Parse JSON action first so that you could easily reference the corresponding field values.

http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

 

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.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,702)