Double Quotation (") - Identification and Removal

[BrandonH]

Hello All, 

I am working with some regulatory language as I talked about in my last post. Link
Having pulled in some new data there is a special character " (double quotes, my assumption) that are not being replaced/cleaned elsewhere in my query.

What Power Query is displaying	"Why is this happening."
What Microsoft Word is displaying	"Why is this happening."
What Notepad is displaying	"""Why is this happening."""

Using a text replacement for the " character doesnt work, using a text replacement for """ doesnt work. Using webbased character identifiers they finds that the characters are double quotes (U+0022). Using a text replacement for #(0022) doesnt work.

Am I missing something glaring here? I'd just edit the source data but it is regularly refreshed (overwritten). Any fixes to the source would be temporary. 

[ppm1]

One thing to try would be to find a cell that has the character and click on it in the query editor. You then see the value in the preview window below where you can select that character, copy it, and paste it into the Replace values pop up.

Pat

[BrandonH · Accepted solution]

Hi Pat, 

I ended up using a few different steps to try to clean the double quotation marks. One thing that I ended up doing was using the text replace function, pointing it to my column and replacing a set of double quotes written as "" with nothing (using the quote structure of M code)  "" so the actual step in my query looks like this: 

= Table.ReplaceValue(#"Replaced Value","""","",Replacer.ReplaceText,{"MyColumn"})

I guess our takeaway here is to replace a double quote character we need to specify a set of double quotes within the M code so power query understands what we are replacing, like escaping a character in other languages/expressions.