Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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! |
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
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! |
You could just use the Trim function form the GUI, which applies Text.Trim, which just removes leading and trailing whitespace.
--Nate
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.