Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sasubhan89
New Member

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

 

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 Capture1.PNG

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

dax
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
dax
Community Support
Community Support

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
Not applicable

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors