Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 re...
General Comment
Need Help
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    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

SKoul's avatar
SKoul
Frequent Visitor
5 years ago

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.