Power Community

Power Community

Query Excel tables using Power Automate

Excel with spaces

This week I was asked three times about how to query Excel files using Power Automate. Time for a SharePains post on this subject.

Is Excel a Database?

I can’t stop myself from starting with a warning. Excel is not a database and it shouldn’t be used as such. Many people however seem to use Excel as a source of data, therefor I’m going to give in and write a post on how to query your data in Excel.

If your Excel however is used as a database, please consider a better place for your data.

Create a basic flow to query Excel

Starting with a very basic flow with a trigger and an action, I’m reading a table form my Excel file stored in OneDrive for Business

Query Excel tables using Power Automate Microsoft Power Automate image 10

The above flow would give me all the items in the Excel table back.

In my case I’ve got an Excel file with a couple of rows of data.

Query Excel tables using Power Automate Microsoft Power Automate image 12

Query data in Excel

Now if we want to query for a number of rows in the table you could configure the Filter Query in the List rows present in table action.

Query Excel using Power Automate

The querying follows the same pattern as I described in my post about the Filter Query setting in the SharePoint Get Items action from a few years back.

So far so easy!

[{"@odata.etag":"","ItemInternalId":"3faa15b0-57c5-4774-a612-c15c0be61e00","Title":"Line 0","Test Column":"Data 0","Number _x0023_":"0","Description":"Also a test"}]

Now that I have some data back, I can see that my field name with a # in it will appear with the hash replaced with _x0023_. You will find that there are a few more characters that are replaced with these codes. So when we want to query by these fields we will have to use these names.

Spaces are also a bit of an issue.

If we were the query by the “Number _x0023_” field. then you might expect something like this to work:

Number _x0023_ eq 0
But you will get the following error:

Syntax error at position 14 in ‘Number x0023 eq 0′.
inner exception: Syntax error at position 14 in ‘Number x0023 eq 0′.
clientRequestId: 80caea27-4f7d-4777-8d16-6adaf18b5336

If only that space was replaced by _x0020_ then we would be able to query by fieldnames with spaces.

How to get around the space query issue – option 1?

Well the only way around this is to get all the rows back and then use the filter array action to filter the results. Not a great solution but the best I’ve found.

Query Excel tables using Power Automate Microsoft Power Automate image 13

The expression in the Filter Array actions are as follows:

item()?['Number _x0023_']
string(0)

How to get around the space query issue – option 2?

If it is possible you might want to remove the spaces from the column names in your excel file. So that there are no spaces there to cause any troubles.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

More Articles Like This

- Advertisement -spot_img