Forum Discussion
Replace unicode character for Dataflow to Dataverse
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.
Hi pgb - it is difficult to help without sample file. The M function looks correct. So I have a couple of ideas to consider:
- Try using the Text.Clean - PowerQuery M | Microsoft Docs function (this sits under Trim in the UI)
- Try searching an replacing "\f" instead of "#"(000C)"
- Try to insert Table.StopFolding step
- 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"
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.