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

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.

When we load this data into Power BI, we will see some Errors and Type Conversion issues.

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

Now lets discuss, how we can Transform these Columns(data) , fix the inconsistent data and finally fix/replace the errors.

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

2) Next Transform the Columns that having the Inconsistent Data

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 =

= Table.TransformColumns(sht_Orders,

    {

     {"Order_Date", each try (if (Value.Type(_)=Date.Type or Value.Type(_)=DateTime.Type) then (_)                                                         else Date.From("12/31/1999"))                  

                                       otherwise Date.From("12/31/1999") , Date.Type },

     {"CntryName", each try (if (Value.Type(_)=Text.Type and (_)<> "N/A") then (_) else "Unknown") 

                                      otherwise "Unknown",Text.Type},

     {"Gross_Sales", each try (if (Value.Type(_)=Number.Type) then (_) else 0 ) 

                                      otherwise 0, Number.Type}

    }, null, MissingField.Ignore

   )

Result:

Notes:

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, 

       {

         {"Order_Date", Date.From("12/31/1999")},

         {"CntryName", "Unknown"},

         {"Gross_Sales", 0}

      })

Result:

Notes:

The complete Power Query is as follows..

let

    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]),

    TransformCols = Table.TransformColumns(sht_Orders,

    {

     {"Order_Date", each try (if (Value.Type(_)=Date.Type or Value.Type(_)=DateTime.Type) then (_)                                                    else Date.From("12/31/1999"))                  

                                     otherwise Date.From("12/31/1999") , Date.Type },

     {"CntryName", each try (if (Value.Type(_)=Text.Type and (_)<> "N/A") then (_) else "Unknown") 

                                      otherwise "Unknown",Text.Type},

     {"Gross_Sales", each try (if (Value.Type(_)=Number.Type) then (_) else 0 ) 

                                        otherwise 0, Number.Type}

    }

   ),

    ReplaceErrors = Table.ReplaceErrorValues(TransformCols, 

       {

         {"Order_Date", Date.From("12/31/1999")},

         {"CntryName", "Unknown"},

         {"Gross_Sales", 0}

      })

in

    ReplaceErrors

# --------------------------------------------------------------------------------- #

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)

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------