Formatted data Cleaning

Posts: 1
Joined: Tue Nov 28, 2017 10:44 pm

Formatted data Cleaning

Postby asttrid » Tue Nov 28, 2017 10:50 pm


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!!

Posts: 922
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Formatted data Cleaning

Postby MattAllington » Thu Nov 30, 2017 5:16 pm

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 394 times
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at

Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest