Replace special characters in a string with Power Automate

Faced an error regarding special characters while creating a new document location in Dynamics 365 CE with Power Automate Flow. To get some context; the reason behind the flow is to extract attachments related to notes and email from Dataverse and store them in SharePoint to save space in Dataverse.

{
  "error": {
    "code": "0x80048054",
    "message": "The relative url contains invalid characters. Please use a different name. Valid relative url names cannot ends with the following strings: .aspx, .ashx, .asmx, .svc , cannot begin or end with a dot, cannot contain consecutive dots and cannot contain any of the following characters: ~ \" # % & * : < > ? / \\ { | }.",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionSourceKey": "Plugin/Microsoft.Crm.ObjectModel.SharePointDocumentLocationService",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepKey": "8a05fcf3-f959-4990-89ff-cc264115cb02",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiDepthKey": "1",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiActivityIdKey": "9ddf2b0f-819f-4461-a74a-f3eefc4dbdae",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiPluginSolutionNameKey": "System",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiStepSolutionNameKey": "System",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionCategory": "ClientError",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionMessageName": "InvalidRelativeUrlFormat",
    "@Microsoft.PowerApps.CDS.ErrorDetails.ApiExceptionHttpStatusCode": "400",
    "@Microsoft.PowerApps.CDS.HelpLink": "http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80048054&client=platform",
    "@Microsoft.PowerApps.CDS.InnerError.Message": "The relative url contains invalid characters. Please use a different name. Valid relative url names cannot ends with the following strings: .aspx, .ashx, .asmx, .svc , cannot begin or end with a dot, cannot contain consecutive dots and cannot contain any of the following characters: ~ \" # % & * : < > ? / \\ { | }."
  }
}

The error occurred when I needed the flow to create the document location referencing to SharePoint if it didn’t already exist. And to create the SharePoint document location reference in Dynamics the name of the table/entity is used. In my case it was on the opportunity table.

The name of the location is created with the name together with the guid of the row that is created. This is the same naming standard as the SharePoint integration in Dynamics uses, and the this handles special characters out of the box. Like the image below:

In order to be able to create document location without facing the error above the special characters had to be stripped from the name of the opportunity before the document location got created. And this led to this post.

In this post I create a sample flow to show how to strip or remove special characters from a string. In the original case a was facing I used the name of the opportunity in the variable stringWithSpecialCharacters. The complete flow is listed in the bottom of the post and further on comes a step by step walk through of the flow.

Step 1

The trigger of the Power Automate flow. This is a button trigger that is easy to use for testing things manually, although any other trigger can be used.

Step 2

Variable contain invalid characters which is used later in the apply to each. This variable can be updated with any characters that you want to replace with another character or value.

Expression used in the step:

createArray('.','@','ß','²','³','µ','`','´','°','^','=','(',')','&','$','§', '~','#','%','*',':','<','>','?','/','|',' ', '  ','{','}','!','+','__','___')

Step 3

Variable with a sample string that contains special characters in different places to show some variety.

Step 4

To variables used to hold values within the apply to each later on.

Step 5

The apply to each step loops through all invalid characters in the invalidCharacters variable. And for each invalid character in the list the special character is replaced with an underscore “_” and updates the variable replaceSpecialCharacter.

The next step in the apply to each is to replace repeating characters. The reason behind this is that when removing a special character and spaces between words it will be replaced with multiple underscores. The step set replaceRepeatingCharacters is solving that by adding ‘__’ and ‘—-‘ to the invalid characters list.

The set stringWithSpecialCharacter step sets the replaced string which can be used to in the name of the document location in my case. To see the result see complete flow at the bottom.

Expressions used in the step

set replaceSpecialCharacters:

replace(variables('stringWithSpecialCharacters'),item(),'_')

set replaceRepeatingCharacters:

replace(variables('replaceSpecialCharacter'),item(),'_')

set stringWithSpecialCharacters:

variables('replaceRepeatingCharacter')

Step 6

The last step outputs the variable containing the formatted string.

To finish of the complete Power Automate Flow is displayed below.

Once in edit mode and one after it has ran.

Overview of flow in edit mode:

Result of the flow run:

Gif of the flow run.

Hope you find it useful.