Gen 2 Dataflow - NaN and Infinity error

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.