topic Re: clean data in Power Query

clean data

gio1082 — Tue, 04 Aug 2020 01:02:47 GMT

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

Re: clean data

Fowmy — Tue, 04 Aug 2020 04:25:27 GMT

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

Re: clean data

mahoneypat — Tue, 04 Aug 2020 11:46:39 GMT

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