Query Excel tables using Power Automate
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
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 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.
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.
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.
Continue Reading Pieter Veenstra’s Article on their blog
Query Excel tables using Power Automate
This week I was asked three times about how to query Excel files using Power Automate. Time for a SharePains post on this subject. I can’t stop myself from starting with a warning. Excel is not a database and it shouldn’t be used as such.
Blog Syndicated with Pieter Veenstra’s Permission
More About This Author
Array
Modern Workplace26 March 2024Receive the available storage within your SharePoint Online tenant- Modern Workplace20 March 2024Options for Documenting Your Power Apps: Comments, Code, and Controls
- Modern Workplace18 March 20242 ways to duplicate SharePoint Lists to support your Power Apps
- Modern Workplace14 March 2024Update a Hyperlink Column in SharePoint with Power Automate
