oData filter query Power Automate - best way to filter file by name

oData filter query power automate: Best way to filter file by name

No comments

 273 total views,  5 views today

In this “oData filter query Power Automate” tutorial, we will learn how to filter files by their names using Power Automate. We will learn how to filter with the name column and with the FileLeafRef column.

Get files with Odata filter on name in Power Automate Get Items

Add the “Get items” or “Get files (properties only)” action.

Add the filter query as follows:


Name eq 'Your file name'

Get files with Odata filter on name in Power Automate Get Items
Get files with Odata filter on name in Power Automate Get Items

Column name does not exist power automate error

Now, let’s run the flow. We will get the below error:

"Column 'Name' does not exist. It may have been deleted by another user.
clientRequestId: 4ac79afe-8bc8-422c-8eee-de02f302ffa8
serviceRequestId: 4ac79afe-8bc8-422c-8eee-de02f302ffa8"
Column 'Name' does not exist. It may have been deleted by another user - Bad request - Action 'Get_items' failed -
Column ‘Name’ does not exist. It may have been deleted by another user

The reason for the above error is that “Name” doesn’t actually exist as a column within the document library. We should include it {} in the query because it is something dynamic that is calculated internally. For example:

 


{Name} eq 'Your file name'

 

The expression is not valid power automate error

Now, if we execute the flow, we will get the below error:

The $filter expression "{Name} eq 'Sample File 1'" is not valid. Creating query failed.
clientRequestId: 4284e396-bb04-4e22-a833-ba2cff50d839
serviceRequestId: 4284e396-bb04-4e22-a833-ba2cff50d839

Note:

  • In the OData filter query, we must include the single quote (”) in the string value, whether it is a dynamic value from the variable or a static string value.

In the above, using both the query formats, we saw that it didn’t work. Now let’s remove the OData filter query in the Get Items action.

Get all items without adding the filter.

Then, loop through each item in the “Get items” query result. Store the file name value in a variable.

Get files with Odata filter on name in Power Automate Get Items apply to each control loop
Get files with Odata filter on name in Power Automate Get Items apply to each control loop

Then add the below condition:


varStrFileName is equal to 'Your file name(without quote)'

 

Notes:

  • The other condition that we have added, which you can ignore,  is to check the empty value in the variable.
  • This solution is not recommended when you have thousands of documents in your library; the performance of the flow execution will be slow. For a better recommended solution, refer to the recommended solution to filter files by name in Power Automate.

Power Automate OData filter query limit: File properties limitation in OData filter query – Get Items or Get files (properties only)

We won’t be able to do the ODATA filter for the below file properties:

Property Name Example
{Name}: “MyFile”
{FilenameWithExtension}: “MyFile.docx”
{Path}: “MyDocumentLibrary/MyFolder/”
{FullPath}: “MyDocumentLibrary/MyFolder/MyFile.docx”
{IsCheckedOut}: false
{VersionNumber}: “1.0”

Use FileLeafRef instead of Name: Recommended way to filter file by name Power Automate

Use the below query to filter file by name:


FileLeafRef eq 'Your file name.extension'

 

Recommended way to filter file by name Power Automate
Recommended way to filter file by name Power Automate

SharePoint Online document library internal names (hidden file properties name)

Below are the file-related internal hidden names:

Internal  Name Description Example
FileLeafRef This is a file name with the extension. “MyFile.csv”
FileRef This is server relative URL path of the file. “sites/GSDRnD/MyDocumentLibrary/MyFolder/MyFile.csv”
File_x0020_Type This is a file type. “csv”,”docx”, “txt” etc.

Now, using the above-mentioned hidden internal name file property, we can filter the files from the SharePoint Online document library using the Power Automate Get Items action.

Summary: filter file by name Power Automate (OData filter query Power Automate)

Thus, in this article, we have learned the following with respect to filtering files by name in Power Automate:

  • How to use the Power Automate Get Items or Get Files (Properties Only) action to filter a file by name.
  • The root cause of the expression is a not valid in Power Automate error.
  • What are the document library’s hidden internal column names?
  • How to use the FileLeafRef property to search for files based on their names.
  • Limitation in Power Automate OData filter query (file properties limitation in Power Automate)

See Also: Power Automate Articles Hub (with real-time example)

If you like our articles, please appreciate our efforts by liking and sharing them with your friends and colleagues. Please join the mailing list to get our articles in your inbox as and when we publish them.

Happy automating and learning!

 

 

About Post Author

Do you have a better solution or question on this topic? Please leave a comment