Expression.Error: We cannot convert the value null to type Number. mysteriously appeared

[janihenr]

I'm working in an ERP reporting project and I have relatively complex PBI and PQ set with dozens of tables and processing steps.

Everything were fine until yesterday when I started to get error Expression.Error: We cannot convert the value null to type Number from several different phases of the processing steps. What makes no sense is that the phases where the error occurs is not how it normally is.

So, this is the end result from one of the tables I'm processing

In the middle of the process eveything works fine. Here is the column item list I suspect is the reason for the errors:

No problems there. This is the applied step #16

The next step #17 is about filtering some rows based on completely other column and which does not have anything to do with the salesorderItem column:

"= Table.SelectRows(#"Added invoicingPeriodWorkingDays", each [invoicingPeriodStarts] <= [invoicingPeriodEndDate])"

And now, when I look at the salesorderItem column after the step #17 I get :

There it is. 

Anyone having seen similar behavior? Is this a memory related problem? Is there any way to get better insight what could cause this?

Jani

[Techzill]

I am working on a powerBi report and I pulled data from Azure cost management. I tried loading the usage details data into powerquery and I got this.

Expression.Error: We cannot convert the value null to type Record.

Details:

Value= Type=[Type

I have tried modifying the M query from the advanced editor but I am not getting it right.

This is the  M query code

let
    source = AzureCostManagement.Tables("Enrollment Number", "10xxxxx", 12, [startDate=null, endDate=null]),
    usagedetails = Source{[Key="usagedetails"]}[Data]
in
    usagedetails

[palash15]

None of this worked for me. Try this:

1. Find the step tranformation step where this error start appearing.

2. go to one step above that (a point where the error does not exist)
3. Select all columns and click "Remove Errors"
4. Go to the next step and the error should be gone.

This seem to work for my transformations.

[fgarcia_e]

That worked for me. This step of removing errors before the point of error has resolved the issue of 

Expression.Error: We cannot convert the value null to type Number

Thanks a lot

[allenabraham]

Finding the source of error is the key to solving the issue. 

One of the ways I found helpful for finding the offending row is to use the "Keep Errors" option and understand the nature of the issue. The next step I take is to replace the error with null. This might or might not be of help to you. 

[hanmahham]

For troubleshooting errors in PBI Power Query Editor, I will extract previous steps (right click on the step where you receive the error) and from there its easier to understand the issue... 

[RobertSlattery]

This happens if there is a step that causes some field in as few as one record to return an error and then a subsequent step does a Grouping that includes the column containing the error result.  For example an AddColumn step.  To find the issue, use Keep Errors to check the steps that might be returning occasional error values.

[mbasha_123]

Hi 
when I am trying to Refresh with more than 70k records its throws errors like

Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=[Type]

Can someone resolve my issue Please

[mtsa225]

I had a similar issue. I noticed there was one row that was blank/null. I removed it by going Home -> Remove Rows -> Remove Blank Row, and then re-doing whatever transformation I neeed to do before getting the error, and am no longer recieving an error.

Here is the PowerBI article I used to fix my issue - Remove blank/null rows: Solved: Delete all the row when there is null in one colum... - Microsoft Power BI Community

[ctedesco3307]

Are there any other ideas for this? I;m getting the same thing - query is super simple and I checked there are no issues 

[janihenr · Accepted solution]

OK, I finally managed to solve this issue. Error message was in the end misleading.

The error "Expression.Error: We cannot convert the value null to type Number" happened already in earlier steps. For some reason it got expanded to all the columns at the later steps.

The root cause was unexpected values in the code list merged in earlier steps.

[Anonymous]

Hi,

Please validate if you have any measure with null values in your model.  If yes, try to replace the null per 0 or filter it out.

It can be a possible solution to this error.

Regards,

[SUMIYU]

HOW DID YOU SOLVE THE PROBLEM. I'M IN TROUBLE WITH THE EXACT SAME PROBLEM. HELP ME~

[RobertP22]

Check each step for errors, it may be your data source. I had this error with a query that referenced another query (query A is the referenced query, query B is the query showing the error). Query A has a conditional column I added, but the data source was missing data in the column targeted by the conditional column. Query A loaded but with errors. Query B references A, but won't load because the errors in the conditional column don't permit query B to download the data. I fixed query A, and this cleared the error in query B.

[janihenr]

Hello,

I would recommend to follow RobertP22's advice. If I remember correctly, the error happened in completely different columns which I was then processing at the later stages. There was nothing wrong in the column which I was processing at the later stages, but for some reason the error was raised by PQ there. Hopefully you get it solved!

BR,

Jani

[Vera_33]

Hi @janihenr 

It might be UI did not load the error (limited rows), you can enable Column quality to see if any error and try to do some error handling?

[janihenr]

Hi Vera,

Thanks for the comment.

Unfortunately column quality indicator displays no error as seen from the below:

I have also tried to Remove Error, Remove Blank Rows, Keep Errors etc,

All I get is

Normally you would get the errorneous rows visible and get more information what's going on but not in this case.

Jani

[SuryaP]

Right Click on the Column which has errors and Click on "Drill Down" which removes Error.

[allenabraham]

This weird step in fact helped me!