Others

How to Handle Apostrophes in Power Automate SharePoint Filter Queries

Muhammad Talha Oct 8, 2026 869 views

Question details

The user needs to resolve an issue where a Power Automate flow fails to retrieve approver details from a SharePoint list because the filter query breaks when processing text values containing apostrophes.

How to Handle Apostrophes in Power Automate SharePoint Filter Queries

Product: Power Automate
Device & OS: not provided
Scenario: Retrieving data from a SharePoint list using an OData filter query based on values imported from Dynamics NAV into an Excel table.
Observed behavior: The SharePoint "Get items" action fails or returns an error because multiple apostrophes in the text value disrupt the OData filter query syntax.

Before you start

Verify the exact internal column name in your SharePoint list and ensure you have edit permissions for the Power Automate flow.

Solution 1Recommended

Escape Apostrophes Using a Replace Expression

Use a Power Automate expression to double the apostrophes in the dynamic content, which escapes them properly for the OData filter query.

In OData filter queries, string values are enclosed in single quotes. If the text itself contains a single quote (apostrophe), the query interprets it as the end of the string, causing a syntax error. To fix this, you must "escape" the apostrophe by doubling it.

Add a Compose action

In your flow, click 'New step' before the SharePoint 'Get items' action and search for 'Compose'. Add the 'Data Operations - Compose' action.

Write the replace expression

Click inside the Inputs field of the Compose action, switch to the 'Expression' tab, and use the formula: replace(item()?['YourExcelColumn'], '''', '''''') — making sure to replace 'YourExcelColumn' with your actual dynamic content.

Update the filter query

In your 'Get items' action, go to the Filter Query field. Set it up as ColumnName eq '@{outputs('Compose')}' to use the safely escaped string.

Escape Apostrophes Using a Replace Expression

Testing the Query: Run a test flow using an Excel row that specifically contains an apostrophe (like "O'Connor") to ensure your expression is escaping the characters successfully.

Solution 2

Filter by Unique ID Instead of Text

Avoid text-escaping issues entirely by mapping and filtering your SharePoint list items using a unique numeric or alphanumeric ID.

Free Microsoft Office alternative

Manage Your Exported Data with WPS Office

While Power Automate and SharePoint handle your workflows, preparing and managing the underlying spreadsheet data exported from Dynamics NAV doesn't require an expensive Microsoft subscription. WPS Office offers a powerful, highly compatible spreadsheet editor to format your tables flawlessly.

1. Download WPS Office: Install WPS Office for free from the official website.
2. Open your data file: Launch WPS Spreadsheet and open the file exported from Dynamics NAV.
3. Format and save: Format your data as a table and save it in .xlsx format to ensure Power Automate can read it smoothly.

Fully compatible with Microsoft Excel (.xlsx) formats required by Power Automate.Free, lightweight, and fast alternative to Microsoft Office.Familiar spreadsheet interface ensures zero learning curve.Advanced table formatting tools to prepare flawless data for SharePoint integration.

Frequently Asked Questions

Why do apostrophes break SharePoint OData filter queries?

OData queries use single quotes to wrap string variables. When an apostrophe appears inside the text value, the query assumes the string has ended prematurely. The remaining text breaks the query syntax, resulting in a failure.

Can I use the replace expression directly in the Filter Query field?

Yes, you can input the replace() expression directly into the Filter Query field using the Power Automate Expression tab. However, using a separate 'Compose' action beforehand is recommended because it allows you to easily view the escaped output in the run history for troubleshooting.

What if my Excel data contains smart quotes instead of straight apostrophes?

Power Automate recognizes smart quotes (curly quotes) differently than standard straight apostrophes. While they usually don't break the OData syntax, they might cause exact-match queries to fail if SharePoint stores them as straight quotes. You may need to use an additional nested replace() function to standardize the quotation marks.

Office Quick Solutions