I have to mention that steps above are separated to show you how the output of try expression looks like. I always recommend using TRY method to get rid of errors that might stop the whole solution to work properly. This method is great error handling method when an error out of blue happens in your data set. but you can return error message if you like with. In this example I just returned zero if I find error. In expression below if I find an error I will return zero as the result. So I can add a custom column with a condition on HasError to see if record contains error or not. The record has two columns: HasError (which says does this record contains error or not), and Error Record (which would be the error happened in details). Result set this time would be a Record for each calculation. Here is how I change the calculation of Revenue Per Item: = try / Fortunately errors can be simply found by TRY keyword. Error OutputĮrror in sample above happened when one of the values is not number. Now Let’s look at each output separately. if zero divided by zero then result would be NaN! and in case of dividing a number by string or reverse there will be an error raised. if one of the values be null, then result set would be null. if number is divided by a zero value result would be positive or negative infinity (depends on the number). Now If I apply a simple division calculation as a new custom columnĪs you see the result set has different outputs depends on the inputs. As you can see Quantity column shows the data type as numeric and text. Here is the data set loaded into Power Query. Now let’s bring the table into Power Query (Excel or Power BI) and apply the division Simple Division Calculation
In table above there are nulls, texts, zeros, negative, and positive values. and as a simple calculation I want to find out Revenue Per Item which would be result of /. The table below is some records with Sales Amount and Quantity. Sample Data Setįor this post I’ll use a sample excel file which has most of possible combinations that I might face in a division calculation. In this post I’ll explain some examples of output for division and a method to find these rows. So you can’t just remove error rows simply. One of the most frustrating facts is that not all of these calculations ends up to an error. Power Query behave differently when you divide a number by zero, zero by zero, number by null, and non-numeric values. One of the most error prone calculations is division. Numeric calculations in Power Query depends on the nature of data returns different results. When you work with data it is normal that you apply numeric calculations.
0 kommentar(er)
