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
bazeemuddin
Frequent Visitor

Replace invisible \xa0 between words

I have a Text Column  (name is Summary) with this text

HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Telephone

HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Telephone

HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Telephone

 

I want to Replace all Rows that has

HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,

with nothing

 

I can do this in Python 2 ways using \s or \xa0 ( Python shows \xa0 between words)

1) 

t_repl_str = 'HEALTH\sINFORMATION\sTECHNOLOGY,Telecommunication\sDEPARTMENT\sPROBLEM,'
df.Summary.str.replace(t_repl_str, '')

2)

t_repl_str = 'HEALTH\xa0INFORMATION\xa0TECHNOLOGY,Telecommunication\xa0DEPARTMENT\xa0PROBLEM,'
df.Summary.str.replace(t_repl_str, '')

 

How can I do this in Power BI?

 

This works in Powe BI - Multiple Replace Values

Replace Value HEALTH with nothing

Replace Value INFORMATION with nothing

Replace Value TECHNOLOGY with nothing

etc..

then trim at the end

 

Is there any way to do one-shot instead of multiple replace value?

 

Thanks for reading and your time.

1 ACCEPTED SOLUTION

@bazeemuddin,

Actually, I can't found any invisible characters when I copy your data and paste them into Power BI Desktop. In your scenario, you can add a custom column as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nB19AnxUPD0c/MP8nUM8fT3Uwhxdfbw8/fxd4/UCUnNSU3Oz80tzctMTizJzM9TcHENcAwK8XX1C1EICPJ38nH11Qkoyk/KSc3V8cwrSS0CqlaK1aGquSDFBRn5eanUNtgl2IeGbo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Summary = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Summary", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.End([Summary],Text.Length([Summary])-Text.Length("HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM")))
in
    #"Added Custom"



Regards,
Lydia

 

Community Support Team _ Lydia Zhang
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

9 REPLIES 9
Greg_Deckler
Super User
Super User

You mean like this?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nB19AnxUPD0c/MP8nUM8fT3Uwhxdfbw8/fxd4/UCUnNSU3Oz80tzctMTizJzM9TcHENcAwK8XX1C1EICPJ38nH11Qkoyk/KSc0FKy7IyM9LVYrVGXIGxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Summary = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Summary", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM","",Replacer.ReplaceText,{"Summary"})
in
    #"Replaced Value"

Or do you want to remove the row entirely?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg for your time, My Rows HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Intercom I want to Replace ONLY this part HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM, as I said before, there is a Hidden special Character between the words. In Python this is = \xa0 Simple Replace in Power BI is Not working. Hope this is clear.

@bazeemuddin,

You can run Python script in Power BI Desktop query editor to replace the words you need. Before using Python feature in Power BI Desktop, please make sure that you enable Python script option as described in this article.

https://docs.microsoft.com/en-us/power-bi/desktop-python-in-query-editor

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia for the info.

 

Just wanted to know if this can be donw without Python/R scripts.

 

Thanks anyway.

 

 

@bazeemuddin,

Could you please share the sample data of your table so that I can download and test?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Its already in my 1st post.

 

here again

Summary
HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Intercom
HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Telephone
HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,DSL
HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM,Problem,Telephone

@bazeemuddin,

Actually, I can't found any invisible characters when I copy your data and paste them into Power BI Desktop. In your scenario, you can add a custom column as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nB19AnxUPD0c/MP8nUM8fT3Uwhxdfbw8/fxd4/UCUnNSU3Oz80tzctMTizJzM9TcHENcAwK8XX1C1EICPJ38nH11Qkoyk/KSc3V8cwrSS0CqlaK1aGquSDFBRn5eanUNtgl2IeGbo0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Summary = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Summary", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.End([Summary],Text.Length([Summary])-Text.Length("HEALTH INFORMATION TECHNOLOGY,Telecommunication DEPARTMENT PROBLEM")))
in
    #"Added Custom"



Regards,
Lydia

 

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lydia for your help.

This is one of the alternatives.

 

But I wonder, why the invisible/hidden chars can't be replaced.

Thanks Greg for the reply.

I will try  and will let you know

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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