Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community 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
    Custom

Screenshot 2021-09-07 214419.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community 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
    Custom

Screenshot 2021-09-07 214419.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Thanks for your help @SKoul and @watkinnc. 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.

watkinnc
Super User
Super User

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

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
SKoul
Frequent Visitor

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

SKoul_0-1630920291327.png

 

Hope this helps.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors