Error Handling Using 'try' and 'otherwise' - Master Power Query - Pro Tips 1
Do you know Power BI actually has a way to handle errors ?
Today, we are going to learn how we can handle data type errors in Power BI.
In data world, the data we deal with is never perfect. Inconsistency in data type is one of the most common issue we deal with in our day to day basis. One of the case is when we have both character types data and integer type data in one. Rarely a case, but if you are familiar working with ERP data, the key or columns are stored as character types. Usually, numbers have number of zeroes at the front such as '00001234' in a 8 character field. We can simply remove those zeroes in power query by converting the data type to a whole number type, however, if we have alpha numeric character as well, then we cannot change the data type to a whole number. If we try to do so, then the step will show the characters type values as an error. So, how do we extract numbers from character string.
There are number of ways we can do this, but my favorite power query technique is to use try and otherwise statement. Try and otherwise statement helps us to catch errors in the transformation step and provide an alternative statement if an error is caught. Basically, the statement after 'try' tries to evaluate something, say, change '00001234' to a number, which does not throw an error, however, if we try to change 'RM1234' to a number, then, it will throw an error. In 'RM1234', try statement then takes the transformation to the alternative route which we define after the 'otherwise'.
In this way, we can easily handle errors in our data. 'Try' and 'otherwise' statement can also be used on a table level.
I hope you enjoy this video. Please subscribe for the best Power BI Tutorial and Power BI Tips and Tricks video.
Thanks,
Rosh