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!!
Formatted data Cleaning
-
- Posts: 1019
- Joined: Sun May 04, 2014 4:01 pm
- Location: Sydney, Australia
Re: Formatted data Cleaning
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
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
{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}
= 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")
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training
Power BI Training
Who is online
Users browsing this forum: No registered users and 1 guest