How to split text by a non-breaking space in Power Query

Quick background first, what are a non-breaking spaces and why would you want to use them?

A non-breaking space is a character which looks the same as a normal space, however it prevents software that is rendering the text from inserting a linebreak where that space is. This is useful if you want to control exactly where text may be moved onto a new line, especially with different sized screens etc. The key point for Power BI and Power Query is that these non-breaking spaces have a different unicode value to the normal space.

For example, if you have a section of text and you want to split this text at every space. If you use the normal split by delimiter function you will get an unexpected result.

Incorrect split

let
    Source = Table.FromRecords({[String = "The quick brown#(00A0)fox jumps over the lazy#(00A0)dog"]}),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "String", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv))
in
    #"Split Column by Delimiter"

However, if you change the splitter function slightly to split with multiple characters and then include the unicode reference #(00A0) for a non-breaking space which you can find from various places such as wikipedia. Now you will see the expected result with both spaces and non breaking spaces splitting the text.

Correct split

let
    Source = Table.FromRecords({[String = "The quick brown#(00A0)fox jumps over the lazy#(00A0)dog"]}),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "String", Splitter.SplitTextByAnyDelimiter({" ","#(00A0)"}, QuoteStyle.Csv))
in
    #"Split Column by Delimiter"

• Power-Bi
• Power-Query