Forum Discussion

IanDavies's avatar
IanDavies
Helper III
1 year ago

Unable to Filter - Error references Null value

I am trying to find an error in some logic in a very large table with lots of merged queries, to help me I tried to apply a filter and I get the error:

 

[Expression.Error] We cannot convert the value null to type Logical.

The column is a conditional column that looks at values of other columns to establish its value. I am not allowed to paste the code as it discloses business confidential information  but it is along the lines of

 

= Table.AddColumn(#"Allocate the device to a BU using the Domain field", "BU_Machine_Name",
/* allocate the device to a BU using the domain filed from TableX */
each if [Domain] <> "None" then [Domain]
else if Text.StartsWith([CleanName], "Business 1") then "Full business name"
else if Text.StartsWith([CleanName], "Business 2") then "Full business name"
else "None")

 

I have added "View Column Quality" and that says there are no empty cells

 

how can I best troubleshoot this problem please?

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi IanDavies ,

     

    Please check each step in the power query. Is there a record where the Boolean column value is null?

     

    Best Regards,

    Wearsky

  • IanDavies's avatar
    IanDavies
    Helper III
    1 year ago

    I am not clear on what you are suggesting.

     

    According to Table.Schema, there are 141 columns in this table. The column I am sorting/Filtering on is type text. How can that column contain a null value without giving an error and if it can why does it only give an error when I try to filter it

    I assume that the boolean ones are those listed as Logical.Type but they have nothing to do with the column I am filtering