Forum Discussion
Data Quality rules
Hello all
I am building Data Quality dashboard based on Excel datasets. I have a column and I need to apply these two rules on it and check which row entry satisfy these rules and which doesn't. After that, I will calculate validity percentage.
- Numbers and special characters not allowed. Exception: (.)and (')
- Full Name - Minimum one space mandatory
Can you please help me in getting the syntax for these two rules? Should I used Power Query Editor or DAX?
Hi Anonymous ,
DAX function CONTAINSSTRING() can help.
https://docs.microsoft.com/en-us/dax/containsstring-function-dax
See the following example.
Column = IF ( CONTAINSSTRING ( 'Table'[Column1], " " ) = TRUE () && CONTAINSSTRING ( 'Table'[Column1], "!" ) = FALSE () && CONTAINSSTRING ( 'Table'[Column1], "@" ) = FALSE () && CONTAINSSTRING ( 'Table'[Column1], "#" ) = FALSE (), 1, 0 )Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Here is one way to do your data validation in the query editor. I put some mock values in and separated the steps into different columns for clarity, but you could comine them into a single step if you prefer.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ssPLkhMTi1WitWJVvLPS1UAc8G8kPJ8CE8BIuuTWlKSWlTsn5dTCeb75ZcoKCt4lRaXKKgqQCUVTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.ToList([Test])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Has 1+ Space", each List.ContainsAny([Custom], {" "})),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Letters and Spaces Only", each if List.Count(List.RemoveMatchingItems([Custom], {"A".."Z","a".."z"," "}))>0 then false else true)
in
#"Added Custom2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi Anonymous ,
DAX function CONTAINSSTRING() can help.
https://docs.microsoft.com/en-us/dax/containsstring-function-dax
See the following example.
Column = IF ( CONTAINSSTRING ( 'Table'[Column1], " " ) = TRUE () && CONTAINSSTRING ( 'Table'[Column1], "!" ) = FALSE () && CONTAINSSTRING ( 'Table'[Column1], "@" ) = FALSE () && CONTAINSSTRING ( 'Table'[Column1], "#" ) = FALSE (), 1, 0 )Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
