I see the special characters in the column values when the source is excel

[Sasubhan89]

I have a column in data set and this column is used a relation with other data sets. because of this characters (#(00A0)) the relation is not established in the data sets. the work around is im replacing every values in the column in the query editor ( like from Authorized#(00A0)Retail to Authorized Retail) . But Im looking for a dynamic solution to remove these special characters from the column. Screen shot attached for reference.

@pabburi 

[edhans · Accepted solution]

Try using the CLEAN transformation. Right-Click on the column, select Transform, then Clean. It is supposed to remove special and non-printable characters, like line feeds. See if it fits your needs. It doesn't remove everything, but takes care of a lot.

[dax · Accepted solution]

Hi @Sasubhan89 , 

There is no built-in fucntion to remove custom characters, if you want to remove text between (), you could try below function, you could refer to text-removebetweendelimiters-function-for-power-bi-and-power-query  for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTWcAh2cdZUitWJVnJSVg4BAqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#"," ",Replacer.ReplaceText,{"Column1"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Replaced Value", "Text_RemoveBetweenDelimiters", each Text_RemoveBetweenDelimiters([Column1], "(", ")", null))
in
    #"Invoked Custom Function"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Sasubhan89,

@edhans, I think this is rather a code sequence - i.e. text sting - rather than a special character in the Text.Clean sense.

@Sasubhan89, is this always in this format? Starts with #(, ends with ) and 4 characters long?

Kind regards,
JB

[edhans · Accepted solution]

Try using the CLEAN transformation. Right-Click on the column, select Transform, then Clean. It is supposed to remove special and non-printable characters, like line feeds. See if it fits your needs. It doesn't remove everything, but takes care of a lot.