Cleaning data to remove white space but keep text space for those broken into extra lines

[Anonymous]

Hi, 

I'm trying to remove all the CRLF and whitespaces off my data. Some of the data has extra line breaks and I'd like to remove these. My knowledge in power query and M is very limited but I've read I can remove the white space by using (FORMAT>Clean) which works great however for text that are split by the extra line, it concatenates the text. 

Orginal Data

"These is an example of my line

break."

Cleaned Data - using Format > Clean

"These is an example of my linebreak"

Desired Output

"These is an example of line break"

I'd really appreciate any help to achieve my desired output. Thank you so much in advance

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslILU5VyCxWSMxTSK1IzC3ISVXIT1PIrVTIycxLjcmLyUsqSk3M1lOK1YlWcnQECcTkOTsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Str = _t]),
    Custom = Table.AddColumn(Source, "Tr", each Text.Combine(List.Select(Text.SplitAny([Str], " #(lf)#(cr)"), each _<>""), " "))
in
    Custom

[Anonymous]

Thanks for your help @SKoul and @Anonymous. I've tried both but it doesn't seem to work on my desired output. I've managed to use the Replace Values to clean up the whitespaces and that seems to have worked.

[Anonymous]

You could just use the Trim function form the GUI, which applies Text.Trim, which just removes leading and trailing whitespace.

--Nate

[SKoul]

Hi @Anonymous,

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

Hope this helps.