Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to use string with apostrophe (‘) in Power Automate filter query

Posted on December 23, 2020April 14, 2021 by Tom

“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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

6 thoughts on “How to use string with apostrophe (‘) in Power Automate filter query”

  1. Sandra says:
    March 3, 2021 at 4:49 pm

    You are a lifesaver. This issue has been plaguing us for months and this was just the solution we needed. Thank you so much.

    Reply
  2. Li says:
    September 15, 2021 at 1:09 am

    Thank you so much! You are a lifesaver!

    Reply
  3. Griff Coleman says:
    June 3, 2022 at 3:00 am

    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?

    Reply
    1. Tom says:
      June 12, 2022 at 8:35 pm

      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.

      Reply
  4. Stew says:
    February 13, 2023 at 2:42 pm

    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?

    Reply
    1. Tom says:
      February 22, 2023 at 7:28 pm

      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.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with 1000s of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes