Cannot convert the value null to type logical

Power Query
1

Hello all
My query have been working very well for the past 3 or 4 months. Suddenly, this came up

Capture

Did this happened to any1 b4? So weird, isn’t it?

2

@pedroccamara Your row contains null value somewhere in your data and null is neither True or False. Therefore, when trying to convert to Data Type Logical, it’s giving the error. You have to remove the null from the row(s).

3

Hello @MudassirAli
I believe my error is in my Debits column, which should be meaning, contains 0 or any other value.
Sorry, i didn’t understand, how should i solve this?

4

@pedroccamara Can you check what’s the Data Type in Debit column. Is it set as Whole numbers, Decimal or Logical?

5

The data type is decimal

6

@pedroccamara Can you share the screenshot of the Data Source Parameter?

7

This is the qry for this query

let
Source = Table.Combine({#“Tab MovCBL Result”, #“Tab MovCBL Bal”}),
#“Calculated End of Month” = Table.TransformColumns(Source,{{“DataCTB”, Date.EndOfMonth, type date}}),
#“Removed Other Columns” = Table.SelectColumns(#“Calculated End of Month”,{“DataCTB”, “Conta”, “Débito”, “Crédito”}),
#“Grouped Rows” = Table.Group(#“Removed Other Columns”, {“DataCTB”, “Conta”}, {{“Debits”, each List.Sum([Débito]), type nullable number}, {“Credits”, each List.Sum([Crédito]), type nullable number}})
in
#“Grouped Rows”

8

@pedroccamara Doesn’t look anything odd in here. At what step of the query the error pops up?

9

until the Grouped Rows, everything’s ok. I’ve tried also the values with Int64.Type and also with nothing and it didn’t work

10

@pedroccamara Try to replace type nullable number with type number. If this doesn’t work then our M Query expert @Melissa will chime in.

Thanks!!

11

Yes I did already and it didn’t work

12

Seeing the error message I expect the issue is elsewhere…
Please share a sample

13

Hello
This query is an append from 2 others.
What kind of sample would you need Melissa?

14

Right, examine the columns in the queries BEFORE the append because you are experiencing a mismatch in datatype. If you are unable to resolve it yourself a sample file containing the error is required for further assistance. (That shouldn’t come as a surprise seeing your a frequent visitor on this forum…)

1 Like
15

Hello @Melissa and @MudassirAli
Finally I’ve found the error. It’s all about cleaning. I’m always deleting/cleaning you know, and sometimes, i delete what I shouldn’t. My surprise is that i only noticed now…like after 2 or 3 months…
You’re absolutely right @Melissa and i’ve learn so much here, thanks mainly to you.
So sorry about this. Should have checked all the data before but, in my defense :slight_smile: i thought it was a common error for you experts.
Thanks a lot

1 Like