“The Title field contains values like: “Tom’s column” (with apostrophe) and I need to use the Title in the Power Automate OData Filter Query.”
“The expression “Title eq ‘Tom’s column'” is not valid. Creating query failed.”
Filter Query in Power Automate has always the same format: column name, operator, and value with apostrophe at the start and end. The value is often dynamic content from some other source, e.g. from another SharePoint list. It can have many values, some of them might include an apostrophe. And here comes the problem: apostrophe is used to start and end the value, but what if the value itself contains an apostrophe? Power Automate won’t like it. You get an error message. Expression is not valid, creating query failed. How do you make it work?
Replace() the apostrophe
You must replace the apostrophe. A single apostrophe in a string is translated as a closing apostrophe. But two apostrophes are translated as an apostrophe in the string. So all you must do is to replace 1 apostrophe with 2 using the replace() expression.
And here comes another complication. Also the replace() expression uses apostrophes to define the strings to replace.
replace example to replace value 'abc' with 'def' in string <value>:
replace(<value>,'abc','def')If you replace the string with an apostrophe, you get the same kind of error: part of the string will be considered the closing apostrophe. But the apostrophe has one more function: leading character for special characters. Not only it does start and end the value, it can also mark a special character. And in this case it can mark the special character of… apostrophe! That means, to find an apostrophe in the string you must use 2 apostrophes. And to replace it with 2 apostrophes, you must use 4 of them. And all inside another pair of apostrophes.
replace single apostrophe with 2 apostrophes in string <value>:
replace(<value>,'''','''''')
If you use the expression above in a Filter Query it’ll process also items where the Title contains one or more apostrophes.
…or put the value in a variable
As suggested by Balazs in the comments, another option would be to initialize a string variable and put the value in the variable. Power Automate will then take care of those characters and you can use the variable on the right side of the query.
Summary
When using Filter Query, e.g. to get data from another SharePoint list, you should consider what column you use. Not only the type of the column, but also the data it can contain.
If there’s any chance that the column data will contain an apostrophe, you should be careful and use an expression to replace them. It’s the most problematic character for Power Automate as it starts and closes a value, and as such it’s not possible for the tool to handle it by itself. It’ll not understand that the string didn’t end yet, you must give it a clear instructions.
You are a lifesaver. This issue has been plaguing us for months and this was just the solution we needed. Thank you so much.
Thank you so much! You are a lifesaver!
Thank you very much for your post. You explain clearly what’s going on in one of our problem scenarios. But we need a little more in order to reach a solution or to decide to enforce a prohibition on apostrophes in names. I’m hoping you might offer some further insights:
• We have “project” records in Dynamics 365 that might be named with an embedded apostrophe, e.g. “Fix George’s estate plan”.
• We have written a tool that links different D365 activity types (emails, tasks, etc.) to a project.
• This tool:
o is an instant cloud flow launched from a selected record (e.g. an email message)
o it takes text input to search for the project record by name (e.g. ‘Fix George’s estate plan’)
o and it fails on the problem you describe
• Your use of replace() works nicely to convert a string with an embedded apostrophe into a processable format, but the new version of the string with two apostrophes instead of one will fail to find our targeted project record.
Are we just out of luck hoping to allow names for projects that contain apostrophes?
Hello Griff,
I didn’t work enough with Dynamic 365 to give you a helpful answer. Maybe you could try to replace the apostrophe with it’s percent encoded character ( decodeUriComponent(‘%27’) ) but I can’t tell you if it’ll help.
Is this not a massive security issue. So if you don’t do this and someone enters data into a form – for arguments sake to search for a surname you could put something like surname = ‘test’ or 1 eq 1 ” and then access all records?
Hello Stew,
I don’t see any security issue, if you’re building a flow then you won’t be able to access anything more than you can access normally under your account.
Initiate and set a string variable (SpecialText) with the text which has special characters and then in the odata filter, use Title eq ‘@{variables(‘SpecialText’)}’
and all the pain is gone….
Hello Balazs,
thank you for the tip, I never thought about using a variable to let flow handle it.
I have tested this Replace method on the Title field and the Get items will find the record but my value is in a single like of text column and the Get items could not find the record. Why does the Title field work differently than a Single Line of Text field?
Hello Rob,
I never noticed any difference between the Title and other Single line of text columns.