How to use the Power Query TransformColumns, TRY and OTHERWISE functions to fix the errors in a Dataset | Power BI Blog

The Power Query TransformColumns, TRY and OTHERWISE functions to Transform and fix the errors in a Dataset
Scenario:

Suppose, we have an excel Dataset with some errors in data like #REF!, #N/A as shown below. Also, there is some inconsistency in the data like, Text appearing in the Number column, number is appearing in the Text Columns etc.

The Excel errors like  #N/A , #REF! will be treated as errors in the Power BI as well.

1) First load the Source file/ Backup data file by checking one of its availability:

By using the MQuery functions TRY and OTHERWISE, we can check availability of the source file or its backup file and then we can load.

Source = try Excel.Workbook(File.Contents(DataSource), null, true) otherwise Excel.Workbook (File.Contents (BackupDataSource), null, true),
sht_Orders = Table.PromoteHeaders(Source{[Item=Dataset,Kind=”Sheet”]}[Data],[PromoteAllScalars=true])

Notes:

here, I have used the Parameters to define the DataSource or BackupDataSource and Dataset

DataSource: E:\Tech_Lab\Power BI\DataSets\ds_Sample.xlsx

BackupDataSource: E:\Tech_Lab\Power BI\DataSets\ds_Sample_Backup.xlsx

Dataset:  tbl_OrderDetails

The columns like [Order_Date], [Cntry_Name], [Gross_Sales] are having the inconsistent data which we are transforming as per below:

If each value(_) of “Order_Date” is either Date or DateTime value then return the same value other wise return the Date value as “12/31/1999” for inconsistent data, and then change the datatype to Date.

If each value(_) of “Cntry_Name” is Text then return the same value other wise return value as “Unknown” for the inconsistent data, and then change the datatype to Text.

If each value(_) of “Gross_Sales” is Number then return the same value other wise return the value as 0 for the inconsistent data, and then change the datatype to Number.

TransformCols =

MissingField.Ignore function helps to avoid the missing Filed error, which we generally face when a Field is missing from Source, on which we have applied the Transformation earlier. 

Till now, we transformed and fixed only the inconsistent data, but the Errors like #N/A , #REF! have not been fixed, which we can replace now. 

Please make sure what is the actual error then decide whether it can be Transformed or fixed at Source, as Replacing Errors is not a good choice.

3) Fix/Replace the Errors from the Transformed Data

ReplaceErrors = Table.ReplaceErrorValues(TransformCols, 

Result:

The complete Power Query is as follows..

let

# ——————————————————————————— #

Suppose, In case, if you don’t want to Transform the Original Columns, instead, you wants to create the new Custom Columns for [Order_Date], [Cntry_Name], [Gross_Sales].

We can create the Custom Columns with Transformations applied as follows:

Transform_Country = Table.AddColumn(sht_Orders, “Trans_Cntry”, each try (if (Value.Type ([CntryName])=Text.Type and [CntryName]<> “N/A”) then [CntryName] else “Unknown”) 

otherwise “Unknown”)

Transform_OrderDT = Table.AddColumn(sht_Orders, “Trans_OrderDT”, each try(if (Value.Type (Date.From([Order_Date]))=Date.Type or Value.Type(DateTime.From([Order_Date]))= DateTime.Type) then [Order_Date] else Date.From(“12/31/1999”)) otherwise Date.From (“12/31/1999”))

Transform_Sales = Table.AddColumn(sht_Orders, “Trans_Sales”, each try(if (Value.Type ([Gross_Sales])=Number.Type) then [Gross_Sales] else 0) otherwise 0)