cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
auswtz
Frequent Visitor

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
Super User
Super User

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 beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Super User
Super User

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 beyond their comprehension!

DAX is simple, but NOT EASY!

auswtz
Frequent Visitor

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 @auswtz,

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors