data-quality-power-bi.txt

SELECT 
    [CustomerID],
    [Forenames] AS [Firstname],
    [Surname],
    [DOB],

    -- Rule violations
    CASE 
        WHEN [Forenames] IS NULL OR LEN([Forenames]) < 2 OR [Forenames] LIKE '%[^A-Za-z]%' 
        THEN 'FirstName not in expected format - Text'
        ELSE NULL
    END AS Rule_FirstName,

    CASE 
        WHEN [Surname] IS NULL OR LEN([Surname]) < 2 OR [Surname] LIKE '%[^A-Za-z]%' 
        THEN 'Surname not in expected format - Text'
        ELSE NULL
    END AS Rule_Surname,

    CASE 
        WHEN [DOB] IS NULL THEN 'DoB not valid'
        WHEN [DOB] = '1900-01-01' THEN 'DoB is not accurate'
        ELSE NULL
    END AS Rule_dob,

    -- Count failed rules
    (
        CASE WHEN [Forenames] IS NULL OR LEN([Forenames]) < 2 OR [Forenames] LIKE '%[^A-Za-z]%' THEN 1 ELSE 0 END +
        CASE WHEN [Surname] IS NULL OR LEN([Surname]) < 2 OR [Surname] LIKE '%[^A-Za-z]%' THEN 1 ELSE 0 END +
        CASE WHEN [DOB] IS NULL OR [DOB] = '1900-01-01' THEN 1 ELSE 0 END 
    ) AS FailedRulesCount,

    -- Data quality label
    CASE 
        WHEN 
            (CASE WHEN [Forenames] IS NULL OR LEN([Forenames]) < 2 OR [Forenames] LIKE '%[^A-Za-z]%' THEN 1 ELSE 0 END +
             CASE WHEN [Surname] IS NULL OR LEN([Surname]) < 2 OR [Surname] LIKE '%[^A-Za-z]%' THEN 1 ELSE 0 END +
             CASE WHEN [DOB] IS NULL OR [DOB] = '1900-01-01' THEN 1 ELSE 0 END) = 0 
        THEN 'Valid'
        ELSE 'Invalid'
    END AS Quality

  FROM [dbo].[Customer]