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?

 

 

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