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

Anonymous 

The function does have the problems you describe, and I'm not sure about its internal logic. The scenarios I've used it in before have been without leading and trailing spaces

 

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

michellepace's avatar
michellepace
Resolver III
5 years ago

Thank you all for your replies. In truth, I am struggling to use any of the solutions. Looking at my initial post, I did not explain my requirement sufficiently. If you look at the column name below, it is exactly what I need to do. Does anyone have the line of M-Code I can paste into the Advanced Editor to achieve this? I am happy for Format>Trim to be used as the first step if it simplifies the solution.

 

.pbix and data here: clickme

 

Many thanks once again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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