Replace invisible \xa0 between words

[bazeemuddin]

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.

[Greg_Deckler]

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?

[bazeemuddin]

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.

[v-yuezhe-msft]

@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

[bazeemuddin]

Thanks Lydia for the info.

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

Thanks anyway.

[v-yuezhe-msft]

@bazeemuddin,

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

Regards,
Lydia

[bazeemuddin]

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

[v-yuezhe-msft · 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

[bazeemuddin]

Thanks Lydia for your help.

This is one of the alternatives.

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

[bazeemuddin]

Thanks Greg for the reply.

I will try  and will let you know