Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

HELP! Error: Expression.Error: We cannot convert the value null to type Logical.

Hi,

 

I keep getting an error that states, "Expression.Error: We cannot convert the value null to type Logical. Details: Value= Type=[Type] when it runs the code for my custom column. The only commonality I can identify is that one of my columns ([Issue])are all null.

 

My columns are ordered: [Issue Category], [Issue], [Date], [Error Code], [Report Type],l [C_Status], [D_Status]

 

How would I get the custom column to result in "Review Needed" if any of ([Issue Category], [Issue], [Date], [Error Code], [Report Type]) are null/blank AND [C_Status], [D_Status] = "N/A" or "Completed"?

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    Anonymous Use this:

    let
    Source = Table,
    Result = 
        Table.AddColumn ( 
            Source,
            "Check",
            (CurrentRow)=>
                let
                    CurrentRowList = Record.ToList ( CurrentRow ),
                    ValuesList = List.FirstN ( CurrentRowList, 5 ),
                    StatusList = List.LastN ( CurrentRowList, 2 ),
                    HasNull = List.NonNullCount ( ValuesList  ) <> List.Count ( ValuesList ),
                    HasNACompleted = List.ContainsAny ( StatusList, { "N/A", "Completed" } ),
                    Result = if HasNull and HasNACompleted then "Review Needed" else "Pass"
                in   
                    Result,
            type text
        )
in
    Result

1 Reply

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    Anonymous Use this:

    let
    Source = Table,
    Result = 
        Table.AddColumn ( 
            Source,
            "Check",
            (CurrentRow)=>
                let
                    CurrentRowList = Record.ToList ( CurrentRow ),
                    ValuesList = List.FirstN ( CurrentRowList, 5 ),
                    StatusList = List.LastN ( CurrentRowList, 2 ),
                    HasNull = List.NonNullCount ( ValuesList  ) <> List.Count ( ValuesList ),
                    HasNACompleted = List.ContainsAny ( StatusList, { "N/A", "Completed" } ),
                    Result = if HasNull and HasNACompleted then "Review Needed" else "Pass"
                in   
                    Result,
            type text
        )
in
    Result