Forum Discussion

EaglesTony's avatar
EaglesTony
Post Prodigy
1 year ago
Solved

We cannot convert the value null to type Logical error

I have this logic:

 

= Table.Group(#"Filtered Rows1", {"FeatureKey", "Histories-NonFeatures.AddedorRemovedFromParent"}, {{"Count", each Table.RowCount(_), Int64.Type}})

 

However, it is throwing the "We cannot convert the value null to type Logical" error.

 

Is it one of the 2 fields I am looking that might have a null ?

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi EaglesTony 

    The error doesn't come from your code syntax, but rather from the data itself. The message
    "We cannot convert the value null to type Logical"
    usually indicates that Power Query is trying to evaluate a logical condition on a null value – possibly during internal comparison or grouping logic.

    In your case, it's likely that one of the grouping columns contains null values:

    FeatureKey

    Histories-NonFeatures.AddedorRemovedFromParent

    If either of those has nulls, the grouping may fail because Power Query can't group or compare null values as expected.

    To investigate this, you can do one of the following:

    Enable Column Profiling in Power Query:
    Go to the View tab and turn on "Column profile" and "Column quality". This will help you see if there are any nulls in those columns.

     

    Add a filtering step before the grouping to remove nulls:

    Table.SelectRows(#"Filtered Rows1", each [FeatureKey] <> null and [Histories-NonFeatures.AddedorRemovedFromParent] <> null)
    Then apply your Table.Group step as planned.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Potentially, but the error may also be elsewhere.  Can you show a sanitized version of your Power Query code and maybe some sample data?

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi EaglesTony 

    The error doesn't come from your code syntax, but rather from the data itself. The message
    "We cannot convert the value null to type Logical"
    usually indicates that Power Query is trying to evaluate a logical condition on a null value – possibly during internal comparison or grouping logic.

    In your case, it's likely that one of the grouping columns contains null values:

    FeatureKey

    Histories-NonFeatures.AddedorRemovedFromParent

    If either of those has nulls, the grouping may fail because Power Query can't group or compare null values as expected.

    To investigate this, you can do one of the following:

    Enable Column Profiling in Power Query:
    Go to the View tab and turn on "Column profile" and "Column quality". This will help you see if there are any nulls in those columns.

     

    Add a filtering step before the grouping to remove nulls:

    Table.SelectRows(#"Filtered Rows1", each [FeatureKey] <> null and [Histories-NonFeatures.AddedorRemovedFromParent] <> null)
    Then apply your Table.Group step as planned.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • EaglesTony's avatar
      EaglesTony
      Post Prodigy
      1 year ago

      I'll give this a shot and let you know.

       

      It sometimes times out getting the data from Odata source, I'm wondering if doing this in DAX would be better.

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi,

    It looks like you're encountering the "We cannot convert the value null to type Logical" error with the following logic:

     

    = Table.Group(#"Filtered Rows1", {"FeatureKey", "Histories-NonFeatures.AddedorRemovedFromParent"}, {{"Count", each Table.RowCount(_), Int64.Type}})

     

    This error typically occurs when one of the fields you're grouping by (FeatureKey or Histories-NonFeatures.AddedorRemovedFromParent) contains null values. To resolve this issue, you can add a step to filter out rows where these fields are null before performing the grouping.

    Here's an updated version of your logic:

     

    let
    // Filter out rows with null values in the grouping fields
    FilteredRows = Table.SelectRows(#"Filtered Rows1", each [FeatureKey] <> null and [Histories-NonFeatures.AddedorRemovedFromParent] <> null),
    // Perform the grouping
    GroupedTable = Table.Group(FilteredRows, {"FeatureKey", "Histories-NonFeatures.AddedorRemovedFromParent"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    GroupedTable

     

    This should help you avoid the null value conversion error. Let me know if you need further assistance!

    Thanks
    translation and formatting supported by AI