Dataflow Gen2 MashupException.Error We cannot convert the value null to type Number

[data_4_good]

Hi all,

I am quite new to working with MS Dataflows and would love some help with an issue I am running into. I am getting the following error for a specific table in my dataflow: 

Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Failed to insert a table., InnerException: We cannot convert the value null to type Number., Underlying error: We cannot convert the value null to type Number. Details: Reason = Expression.Error;ErrorCode = Lakehouse036;Message = We cannot convert the value null to type Number.;Message.Format = We cannot convert the value #{0} to type #{1}.;Message.Parameters = {"null", "Number"};ErrorCode = 10277;

I have checked and there are no errors in the query itself. Everything checks out fine there. I have found on some other threads that turning off staging allow the Dataflow to refresh, and I found that to be true. Thouhg this presents a different problem: I was unable to load the table in a Power BI report connected to the Dataflow. I ultimately would like to leave staging on for that table. What can I do to resolve this issue?

Thanks for any and all help! 

[data_4_good]

Thank you @collinq, @rohit1991, and @v-kpoloju-msft for contributing to the conversation. I went in and did find a replace null for 0 on all columns and am still receiving the same error. I am not sure what is going on...

[data_4_good · Accepted solution]

Hi @v-kpoloju-msft, @rohit1991 and @collinq. I found the solution! The issue was with a table I was merging into another. The key was a number, but in one of the tables, the service converted it to text. As a result, the merge returned only null values. When I performed operations on that merged column, the error occurred.

The strangest part is that, in the dataflow preview, the column appeared to merge correctly, making it seem like everything was working correctly. It wasn't until I created another dataflow referencing a pared-down version of the first one that the errors became visible in the preview. Thanks again for everyone reaching out!

[v-kpoloju-msft]

Hi @data_4_good,
Thank you for reaching out to the Microsoft fabric community forum. Thanks @rohit1991, and @collinq, for your inputs on this issue.

I have reviewed the details you provided and have reproduced the scenario on my end, where it works correctly. Please follow the steps below, which may resolve the issue.

Please open your workspace, create a Dataflow Gen2 activity, and then import your data into Dataflow Gen2.

Right click on the Sales Amount in the sample data, select the option Replace values, and follow the steps shown in the image below:

Once the task is completed, replace the values, and it will display numbers instead of null values:

I have provided alternative workarounds here that may resolve the issue in addition to replacing the values:

• Instead of converting data types immediately, first clean up the data, then apply Number.FromText() at the final step.
• Instead of replacing null values directly, you can use the try function in Power Query M to handle conversion errors dynamically.

kindly refer the below link for more information:
Replace values and errors - Power Query | Microsoft Learn
Table.ReplaceValue - PowerQuery M | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[rohit1991]

Hi @data_4_good 

This error usually means Power Query is trying to convert a column to a number type but hits unexpected nulls or text values during staging.

Instead of just replacing nulls, also check if the merge introduced unexpected text in a numeric column. That’s a common issue—especially if two tables have different data types for the same key.

You can also wrap the conversion using try Number.From(...) otherwise null in Power Query to prevent it from breaking. It handles type mismatches more safely.

[data_4_good]

Hi @collinq, 

Thanks for helping me out here. I’ll do some more digging into the data to see if I can find the issue.

One thing I find confusing is that the error message seems to suggest that a column with a numeric data type cannot contain null values. I always thought that was allowed. Am I misunderstanding something, or could this be an issue with how the data is being processed in the mashup?

[collinq]

HI @data_4_good ,

This is one of those generic and common-ish messages.  But, you may not have any errors in your queries. Desktop can handle these things becuase it basically ignores them but Service does not ignore these types of problems.

The issue is in a specific value in a specific column.  So, i find this is commonly a Date type of field.  The best way to find this is to slog through your data and find the date columns and then sort it ascending and descending to see if you have an abnormal field.

Also, the message clearly states that this is a number/null problem so do the same with any columns that you converted to a number.  The issue  could be something subtle - like a space and not a null, or a blank and not a null or a field that has a character in it.