none
How to replace special characters RRS feed

  • Question

  • Hi,

    I'm trying to use powerquery to get a table of names and values from a webpage, but there is a special character in the middle of the value, the ASCII code 160.

    For example, if the value is 550320 it's written as 550 320 with the 160 character in the middle (it seems a space, but it's not).

    How can I remove or replace this special character ?

    Thank you !


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

    Thursday, April 14, 2016 3:47 AM

Answers

  • Hello,

    the nonbreaking space is the Hex code 00A0, so you need to use 

    #(00A0)

    in the replace command. When you enter this in the dialog, Power Query will add some extra characters and you will need to clean up the M code. From the dialog, Power Query generates

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

    Clean it up to be

    = Table.ReplaceValue(#"Trimmed Text","#(00A0)","",Replacer.ReplaceText,{"value"})


    cheers, teylyn

    • Marked as answer by Dennes Thursday, April 14, 2016 7:00 AM
    Thursday, April 14, 2016 6:01 AM

All replies

  • Hello,

    the nonbreaking space is the Hex code 00A0, so you need to use 

    #(00A0)

    in the replace command. When you enter this in the dialog, Power Query will add some extra characters and you will need to clean up the M code. From the dialog, Power Query generates

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

    Clean it up to be

    = Table.ReplaceValue(#"Trimmed Text","#(00A0)","",Replacer.ReplaceText,{"value"})


    cheers, teylyn

    • Marked as answer by Dennes Thursday, April 14, 2016 7:00 AM
    Thursday, April 14, 2016 6:01 AM
  • Thank you !


    Dennes - Se resolveu, classifique a mensagem, por favor - [http://www.bufaloinfo.com.br] NOVO DVD Segurança no ASP.NET : http://www.bufaloinfo.com.br/LearingSeriesSegurancaASPNET2.asp

    Thursday, April 14, 2016 7:05 AM