Replace unicode character for Dataflow to Dataverse

[pgb]

Hello,

I have been going nuts trying to figure this out. I'm pulling data from an Oracle database and I have a column, PRPSL_ABSTRACT, that contains text (Oracle format VARCHAR(4000)), and some of the text values contain things like \f and other unicode characters. I'm trying to insert the data into a text Dataverse column via a Dataflow. 

I read the post linked below (and many others) and I still can't get this to work. 

https://community.powerbi.com/t5/Power-Query/Replacing-Unicode-character-non-printable-characters/m-...

The actual error is:

{"error":{"code":"0x80040278","message":"Invalid character in field 'cr812_prpsl_abstract': '\f', hexadecimal value 0x0C, is an invalid character."}}

The code I'm using is:

Table.ReplaceValue(#"Removed columns", "#(000C)", "", Replacer.ReplaceText, {"PRPSL_ABSTRACT"})

000C is the encoding sequnce for \f. https://www.compart.com/en/unicode/U+000C 

When I add this as a step to clean up my data and re-publish my Dataflow the exact same error with the \f characters keeps appearing. I have no idea why they aren't being stripped out. Any help is greatly appreciated! 

[Daryl-Lynch-Bzy]

Hi @pgb - it is difficult to help without sample file.  The M function looks correct.  So I have a couple of ideas to consider:

1. Try using the Text.Clean - PowerQuery M | Microsoft Docs function (this sits under Trim in the UI) 
2. Try searching an replacing "\f" instead of "#"(000C)"
3. Try to insert Table.StopFolding step
4. It might be necessary to change the Text.Encoding using the Text.FromBinary function

The following example. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLlFwSSxJVIrViVbySCxWyMsvyk3MUSguSExOLYaJxqSFZ2SWpMakBUOFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Clean, type text}}),
    #"Replaced WhiteSpace" = Table.ReplaceValue(#"Cleaned Text","#(000C)","",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Normal Space" = Table.ReplaceValue(#"Replaced WhiteSpace"," ","",Replacer.ReplaceText,{"Column1"})
in
    #"Replaced Normal Space"

[pgb · Accepted solution]

Thank you for the quick response. I think I finally figured it out! I had been using the Clean function as you mentioned, but the problem is that it stripped out too much and was leaving me with giant blocks of unformatted text. 

I noticed when I used the Replace Values dialogue and typed in something like #(000F) it was creating a formula with an erroneous #(#) appended. Not sure why that is. 

Table.ReplaceValue(#"Replaced value 3", "#(#)(000F)", "", Replacer.ReplaceText, {"PRPSL_ABSTRACT"})

The correct version is below.

Table.ReplaceValue(#"Replaced value 3", "#(000F)", "", Replacer.ReplaceText, {"PRPSL_ABSTRACT"})

You'll notice that I had the correct version listed in my original question and it still wasn't working at the time. Related to that, I noticed that my final joins didn't seem to be refreshing after I made the filter changes. Do queries need to be manually refreshed after you make any changes to related tables? I just assumed that would be automatic.