Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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. ...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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