Forum Discussion

pgb's avatar
pgb
New Member
4 years ago
Solved

Replace unicode character for Dataflow to Dataverse

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-p/2023894#M59549

 

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! 
 
 
 
Need Help
  • pgb's avatar
    pgb
    4 years ago

    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. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      pgb
      New Member
      4 years ago

      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.