Forum Discussion

michellepace's avatar
michellepace
Resolver III
5 years ago
Solved

type Text: Clean in-between white space

Hi. Is there a way to parse text to single white spaces only?   That is: "mary    white     helloooo"   Becomes: "mary white helloooo"
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    try this

     

     

    let
    Source = Excel.Workbook(File.Contents("C:yourpath\Q.14 Data.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Single space everything, and trim both ends (don’t make a new column)", type text}}),
    tc=Table.TransformColumns( #"Changed Type", {"Single space everything, and trim both ends (don’t make a new column)", each Text.Combine(List.Select(Text.Split(_," "), each _<>""), " ")})
in
    tc

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

= Splitter.SplitTextByWhitespace()(" mary white helloooo ")

 

this function, that of which I now know the existence thanks to ziying35 , for a string with leading or trailing whites spaces, gives this result

 

 

where the first and last element are the empty string "".

 

Why?

 

what kind of internal logic is applied in your opinion?

 

 

Bohumil_Uhrin's avatar
Bohumil_Uhrin
Helper II
5 years ago

Anonymous im not sure, but maybe Splitter.SplitTextByWhitespace() considers more spaces in a row ("      ") as a single "whitespace"... thats why there is only one empty element at the beginnning and end of the list:

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    try to write, for example, in pseudo-code (simulating a low-level language) the various steps that produce that result.
    Let's see where the different management of leading and trailing spaces intervenes with respect to those inside the text string.

     

     

     

    PS

    Un empty element "" is different from blank " "