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

     

     

     

AlB's avatar
AlB
Community Champion
5 years ago

Hi michellepace 

Paste this M code in a blank query to see the steps. The recursive function is the relevant one.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk0sqlQAgvKMzJJUEEMhIzUnJx8IlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    fx = (input as text) => let reduce = Text.Replace(input,"  "," "), res=if Text.Length(reduce)=Text.Length(input) then input else @fx(reduce) in res,  
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1]))
in
    #"Added Custom"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers