First of all, filtering on words or names should be avoided. It’s always better to filter on an ID. Filtering words or names brings difficult challenges like this. My recommendation is to reflect on your architecture so that you can avoid filtering on other things then ID’s. Because I had to investigate an issue, I share my experience on how to (temporary) fix it with you. The moment someone uses another character then a single quote, your flow fails again.
Filtering data with the SharePoint action in Power Automate is possible for a long time. In this blog post we focus on the “Get items” action of the SharePoint connector in combination with a special character, namely the single quote or apostrophe.
For other special characters, we haven’t found a solution yet. We already tried multiple options like the uriComponent() or encodeUriComponent() function but non of those were an option. A deep dive into oData (Open Data Protocol) is required to investigate additional special characters.
A simple SharePoint list called “Cars” will contain a row “Wouter’s Car”. Remark the single quote or apostrophe in the text.
The most logic approach would be to build our flow like below. This will not work and will return an error.
The error will be “Expression is not valid. Creating query failed.”.
If we add a simple expression around the dynamic variable, the single quote or apostrophe will be recognized as it should. See screenshot below.
replace(variables('DynamicValue'),'''','''''')
