Data Quality rules

[Anonymous]

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.

1. Numbers and special characters not allowed. Exception: (.)and (')
2. 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?

[Anonymous · Accepted solution]

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.

[amitchandak]

@Anonymous ,text.contains can help

https://docs.microsoft.com/en-us/powerquery-m/text-contains

[mahoneypat]

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