remove non breaking space in power query

user_13562186 0
Viewed 185

Just come across an interesting issue where PQ isn't transforming seemingly identical data in the same way.

As an example here I just wish to replace 24 mg/kg bw/day with Hello:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","24 mg/kg bw/day","Hello",Replacer.ReplaceText,{"Column1"})
in
    #"Replaced Value"

enter image description here

We can see that there is one instance (in red) where this doesn't work and this is due to on-breaking spaces. I am unsure how to deal with this and wondering if anyone has run into a similar issue.

Data

24 mg/kg bw/day
24 mg/kg bw/day
24 mg/kg bw/day
non-breaking-characters powerquery excel
4 Answers

Add a step to replace non-breaking spaces.

I had a similar issue with a slash (/) vs a divide-by symbol that looks almost exactly the same.

user_71583800 
Custom1 = {
Character.FromNumber(32),
Character.FromNumber(160),
Character.FromNumber(5760),
Character.FromNumber(8194),
Character.FromNumber(8195),
Character.FromNumber(8196),
Character.FromNumber(8197),
Character.FromNumber(8198),
Character.FromNumber(8199),
Character.FromNumber(8200),
Character.FromNumber(8201),
Character.FromNumber(8202),
Character.FromNumber(8239),
Character.FromNumber(8287),
Character.FromNumber(12288),
Character.FromNumber(8203)

    },
    Custom2 = List.Accumulate(Custom1, #"Split Column by Delimiter1", (table, old) => Table.ReplaceValue(table, old," ", Replacer.ReplaceText, {"POD"})),
user_135621860 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Replace([Column1], Character.FromNumber(160), " ")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","24 mg/kg bw/day","Hello",Replacer.ReplaceText,{"Custom"})
in
    #"Replaced Value"
user_183450370

To replace the entire table

#"Replaced Value" = Table.ReplaceValue(previous step,"#(00A0)","",Replacer.ReplaceText,Table.ColumnNames(previous step))

where U+00A0 No-Break Space (NBSP) Unicode Character

user_107281810
Related