How to use string with apostrophe (‘) in Power Automate filter query
“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.
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.