Excel Filter Query on a Number Column in Power Automate

A few years back I wrote a post about how to write a filter query for Excel in Power Automate. Today I found an issue with number columns in filter queries while working with a client on a flow.

My example Excel file

I’ve created an Excel file with a column containing numbers and a column that has text.

Now, I want to run a flow that queries for a single row in my table.

From my previously mentioned post we know that we can run the following queries to get the third row only

ID eq 3
ID eq '3'
Details eq 'Three'

I’ll put this to the test

First I’m creating a flow without any filter query.

Now we are getting the following output for the 6 rows. We can see that the ID field is returned as text.

"value": [
            {
                "@odata.etag": "",
                "ItemInternalId": "c0ed1dca-0841-478b-bed0-bb399fa664f3",
                "ID": "1",
                "Details": "One"
            },
            {
                "@odata.etag": "",
                "ItemInternalId": "8ca6e464-f316-47ab-accf-a7e01f48ffa5",
                "ID": "2",
                "Details": "Two"
            },
            {
                "@odata.etag": "",
                "ItemInternalId": "a84cdfe1-91e7-41f5-9c6c-33baa91bf1b4",
                "ID": "3",
                "Details": "Three"
            },
            {
                "@odata.etag": "",
                "ItemInternalId": "0de6e22f-bb1e-4a71-8e64-b1e5cc09877d",
                "ID": "4",
                "Details": "Four"
            },
            {
                "@odata.etag": "",
                "ItemInternalId": "f8a866ca-fc9e-4f01-b401-9da3378e5065",
                "ID": "5",
                "Details": "Five"
            },
            {
                "@odata.etag": "",
                "ItemInternalId": "075aecb3-fb3b-4a2f-adae-0866b616a1be",
                "ID": "6",
                "Details": "Six"
            }
        ]

And even if we turn the ID column into a number, currency or any of the other number alike cell types, the number will still be returned as text.

Therefore if we want to query by the ID column we will most likely need to use the following expression.

ID eq '3'

Query Excel on a number column

So I’m updating my flow to include the filter query setting on the ID column.

And we are getting the expected result back.

"value": [
            {
                "@odata.etag": "",
                "ItemInternalId": "51b9d60a-7b6f-4b16-9f3b-054853aa0152",
                "ID": "3",
                "Details": "Three"
            }
        ]

So this works for me, but for my client it does not work. After a lot of trying out various different roads, I found that querying a number field worked for the first 99 rows but any rows beyond the first 99 rows would fail.

As you can see below when I query the row 100 I’m getting nothing back and then

And then just a couple of minutes later as I figured out that we can’t query number field beyond the 100 rows….

Microsoft installs an update of the Excel connector at 4pm (UK time) and all works again.

Well it is nice to see that Microsoft installs fixes when we need them, but this has been a very annoying issue.