clean data

[gio1082]

Hi, 

I have a data that I want to clean. I want to remove data from a row if its has "DOC" (ex 10265049DOC), and if less then 7 chracters (Ex: 12MM,4, 123m33) but keep anything data that  starts with an S  (S0644, S000012, S012)

thank you

[mahoneypat]

Just to clarify.  Are each of the values on separate rows?  Or are you trying to keep just parts of longer strings on each row?  If the former, does this approach work

1. Turn on the Formula Bar on the view tab (check the box)

2. Select the column with text values and, on the Transform tab, do a Format step (add prefix, lowercase, etc.).  It doesn't matter which one, as you are just using this to generate most of the code you'll need.  You'll get a step added like this

= Table.TransformColumns(#"Changed Type",{{"TextColumn", Text.Lower, type text}})

3. Replace the Text.Lower part (or whatever function you chose) with the code shown below

= Table.TransformColumns(#"Changed Type",{{"TextColumn", each if Text.StartsWith(_, "S") then _ else null}})

This will keep only the values that start with "S" and replace the rest with null.  You can added the expression as needed to get your desired logic.  "_" just refers to the value in that column on that row.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Fowmy · Accepted solution]

@gio1082 

In Power Query, Add the below code as new Custom Column then filter by FALSE, you get as per your requirement.

 = if  Text.Start([DATA],1) = "S" then false else Text.Contains([DATA],"DOC") or Text.Length([DATA]) < 7

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn