none
Can't Split Column - Non-breaking space (string shift space) RRS feed

  • Question

  • Hi
    I try to import data from a web page into Power Query.
    The text contains a non-breaking space like "°" (=> string shift space in Word).

    I tried to replace it without success.

    Neither replace "space" nor one of the special character available helped to solve this problem.

    Does anybody know how to solve it?

    Thanks in advance



    Tuesday, February 23, 2016 11:15 AM

Answers

  • You have to catch and isolate the troublemaker in order to pass him to the replacement-function like this:

    = Table.ReplaceValue(Source,SpecialCharacter,"o",Replacer.ReplaceText,{"Column1"})

    Say you have a field in your Excel Sheet containing a letter followed by this special character named "SpecialCharacter", then the isolation would work as follows (queryname= SpecialCharacter:

    let 
    
    Source = Excel.CurrentWorkbook(){[Name="SpecialCharacter"]}[Content],
    
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.ToList([Column1])),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        Custom = #"Expanded Custom"{1}[Custom]
    in
        Custom
    
    Have a look at the file.

    Imke Feldmann TheBIccountant.com

    Tuesday, February 23, 2016 2:10 PM
    Moderator

All replies

  • You have to catch and isolate the troublemaker in order to pass him to the replacement-function like this:

    = Table.ReplaceValue(Source,SpecialCharacter,"o",Replacer.ReplaceText,{"Column1"})

    Say you have a field in your Excel Sheet containing a letter followed by this special character named "SpecialCharacter", then the isolation would work as follows (queryname= SpecialCharacter:

    let 
    
    Source = Excel.CurrentWorkbook(){[Name="SpecialCharacter"]}[Content],
    
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.ToList([Column1])),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        Custom = #"Expanded Custom"{1}[Custom]
    in
        Custom
    
    Have a look at the file.

    Imke Feldmann TheBIccountant.com

    Tuesday, February 23, 2016 2:10 PM
    Moderator
  • Thanks very much

    It worked!

    Didn't know this trick ....

    Tuesday, February 23, 2016 2:48 PM
  • brandnew :-)

    Imke Feldmann TheBIccountant.com

    Tuesday, February 23, 2016 2:49 PM
    Moderator
  • Yes, I know.
    In my case this problem appeared when I applied Text.Lower() for all column.
    And all of spaces become non-breaking with UNICODE 160

    #"YourStep" = Table.ReplaceValue(#"StepBefore",Character.FromNumber(160),

    " ",Replacer.ReplaceText,{"ColumnName"})

    I hope this will help you understand the absurdity of the situation=)
    Friday, July 29, 2016 11:32 AM