Gen 2 Dataflow - NaN and Infinity error

[MDaiy]

Hello,

I am getting the following error when attempting to publish my Gen 2 Dataflow: 

Statement: Error Code: Mashup Exception Data Source Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Microsoft SQL: Error converting values NaN or Infinity to type 'FLOAT' in the column 'Column6'. NaN and Infinity are not supported.

From my understanding, this means that the NaN/Infinity values exist in the 6th column from the left in the Statement table. However, no changes have been made to Statement or anything upstream of it (as far as I can tell?)

Research tells me this is likely caused by a division by zero somewhere, but I have logic that accounts for this..

Any help on with uncovering the cause of this error would be greatly appreciated!

[v-ssriganesh]

Hi @MDaiy,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @MDaiy,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[MDaiy]

I have the following transformation applied to account for this when all values are in a single unpivot column (which has worked until now): 

#"Filtered rows" = Table.SelectRows(#"Unpivoted columns", each (not Number.IsNaN([Value]) and [Value] <> 0 and [Value] <> null and [Value] <> "")),

[v-ssriganesh · Accepted solution]

Hi @MDaiy ,
Thanks for posting your query in Microsoft fabric community forum.

your current filter is handling NaN values but may not be accounting for Infinity values, which could be causing the issue. Additionally, if [Value] is stored as text instead of a number, Number.IsNaN([Value]) may not work as expected.

• Try modifying your transformation step with below M code:
  
  #"Changed Type" = Table.TransformColumns(#"Unpivoted columns", {{"Value", each try                   Number.From(_) otherwise null, type number}}),
  #"Filtered rows" = Table.SelectRows(#"Changed Type", each (not Number.IsNaN([Value]) and not Number.IsInfinite([Value]) and [Value] <> 0 and [Value] <> null and [Value] <> ""))
  
  
• The above M code ensures that [Value] is properly converted to a number before filtering. It filters out both NaN and Infinity values, which are not supported in SQL FLOAT columns. Additionally, it handles potential text-based NULL values that might be affecting the transformation. Please update the above M code and refresh the Dataflow.
  
  
• Verify any unexpected values in [Value] by using the following debug step:

         Table.Distinct(Table.SelectColumns(#"Unpivoted columns", {"Value"}))

This will help identify any unusual data points that might be causing the issue. If this resolves your problem, please accept it as a solution and leave a "Kudos" so other members can find it more easily.
Thank you.

[v-ssriganesh]

Hi @MDaiy,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.