Forum Discussion
Cleaning data to remove white space but keep text space for those broken into extra lines
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
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
4 Replies
CNENFRNLCommunity Champion
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 CustomThanks 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.
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 tcHope this helps.
