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

     

     

     

ziying35's avatar
ziying35
Impactful Individual
5 years ago

Hi, michellepace 

Try this:

= let str="mary    white     helloooo" in Text.Combine(Splitter.SplitTextByWhitespace()(str), " ")

If my code solves your problem, mark it as a solution 

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?