Forum Discussion
clean data
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 🙂
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