Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |