cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

clean data

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

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Super User IV
Super User IV

@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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors