Issue: Power BI not recognizing null or blank values in few specific columns

Hi srinrealyf,

Did you check for Non-Printable Characters?

The most likely culprit is that your "null" values contain non-printable characters like spaces, tabs, or other whitespace, you can try this to fix this issue:

= Table.AddColumn(#"Previous Step", "CheckBlanks", each 
    let
        trimmed = Text.Trim(Text.From([column_a])),
        clean = Text.Clean(trimmed),
        isActuallyBlank = 
            [column_a] = null 
            or trimmed = "" 
            or clean = "" 
            or Text.Length(clean) = 0
    in
        isActuallyBlank
)

Second Approach: 

• Create a custom column that checks for all possible blank scenarios:

= Table.AddColumn(#"Previous Step", "IsActuallyBlank", each 
    let
        textValue = Text.From([column_a]),
        cleanValue = Text.Clean(Text.Trim(textValue))
    in
        if [column_a] = null then "Null"
        else if textValue = "" then "Empty String"
        else if cleanValue = "" then "Whitespace"
        else if Text.Length(cleanValue) = 0 then "Zero Length"
        else "Has Value: " & textValue
)

• Or you can try these more comprehensive DAX measures:

Blank Count Comprehensive =
VAR CleanValue = TRIM(REPLACE('Table'[column_a], UNICHAR(160), UNICHAR(32)))  // Replace non-breaking spaces
RETURN
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[column_a] = BLANK() ||
        ISBLANK('Table'[column_a]) ||
        CleanValue = "" ||
        LEN(CleanValue) = 0
    )

// Alternative approach using FILTER
Blank Detection = 
COUNTROWS(
    FILTER(
        'Table',
        ISBLANK('Table'[column_a]) ||
        TRIM('Table'[column_a]) = "" ||
        LEN(TRIM('Table'[column_a])) = 0
    )
)

Third Approach : 

• Add these transformation steps in Power Query:

// Step 1: replace various null-like values
= Table.ReplaceValue(#"Previous Step",each [column_a],each if [column_a] = null then null else Text.Trim(Text.Clean(Text.From([column_a]))),Replacer.ReplaceValue,{"column_a"})

// Step 2: replace empty strings with null
= Table.ReplaceValue(#"Previous Step","",null,Replacer.ReplaceValue,{"column_a"})

// Step 3: additional cleaning for special whitespace
= Table.TransformColumns(#"Previous Step", {{"column_a", 
    each if _ = null then null 
    else Text.Trim(Text.Clean(Text.Replace(_, "[^\u0000-\u007F]+", " ")))
}})

For quick analysis create a calculated column that explicitly marks the problematic values:

Clean Column = 
IF(
    ISBLANK('Table'[column_a]) || 
    TRIM('Table'[column_a]) = "" || 
    LEN(TRIM('Table'[column_a])) = 0,
    "BLANK",
    'Table'[column_a]
)

You can create a calculated table for testing:

Test Table = 
SELECTCOLUMNS(
    FILTER(
        'Your Table',
        ISBLANK('Your Table'[column_a]) || 
        TRIM('Your Table'[column_a]) = ""
    ),
    "Blank Values", 'Your Table'[column_a],
    "IsBlank Result", ISBLANK('Your Table'[column_a]),
    "Trim Length", LEN(TRIM('Your Table'[column_a]))
)

Also don't Forget to check the Data Source:

• Add a diagnostic column to see what's actually in your data:

= Table.AddColumn(#"Previous Step", "Debug_column_a", each 
    let
        raw = [column_a],
        asText = Text.From(raw),
        charCodes = Text.ToList(asText),
        codes = List.Transform(charCodes, each Character.ToNumber(_))
    in
        [
            RawValue = raw,
            AsText = asText,
            Length = Text.Length(asText),
            CharacterCodes = codes,
            IsNull = raw = null
        ]
)