Formatted data Cleaning

[asttrid]

Hi,

I have a column in PowerQuery containing values, of which most of them are structured in a fixed format (e.g. xxx xxxx xxxx xxxx), and some of them are just random values (due to errors in the dataset).
Is there any way in PQ that allows me to keep only the values with the fixed format or change all the values that do not have that format into "null"?

Filtering & next changing the random values to null is no option as these random values will keep changing.

I hope someone can help me out.
Many thanks!!

[MattAllington]

How about this as an approach.

1. Duplicate the column
2. Split on the space character
3. add a custom column that checks the length of the 3 columns to make sure they are 3, 4, 4 respectively

Custom column

Code: Select all

= Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Length([#"Numbers - Copy.1"]) = 3 and Text.Length([#"Numbers - Copy.2"]) = 4 and Text.Length([#"Numbers - Copy.3"])=4 then "Correct" else "Dud")

split.png (897.8 KiB) Viewed 1156 times