remove non breaking space in power query-CodePudding

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"

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

CodePudding user response：

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.

CodePudding user response：

= Table.ReplaceValue(#"Changed Type","#(00A0)","",Replacer.ReplaceText,{"Column1"})

CodePudding user response：

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"