1

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
Nick
  • 789
  • 5
  • 22

3 Answers3

0

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.

JSmart523
  • 2,069
  • 1
  • 7
  • 17
  • how would you add this step? I have tried but nonbreaking spaces character doesn't seem to be recognised – Nick May 26 '22 at 10:57
  • Non-breaking space is one of the special characters listed in the dialog. – Rory May 26 '22 at 11:08
0
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"})),
Nick
  • 789
  • 5
  • 22
0

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

xyz333
  • 679
  • 6
  • 14