OData Filtering on Excel Column with Spaces in Header

(0) Share

Report

Posted on by SteveD

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?

Many thanks,

- Steven

Categories:

Using connectors

I have the same question (0)

All responses (3)

Answers (1)

Sort by

ScottShearer 25,302 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

RezaDorrani 12,145 on at

Like
a
(3)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-bacao-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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-Excel/idi-p/297122

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,

Was this reply helpful? Yes No