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.
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
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
)
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:
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
——————————————————————————————————–
Be the first to comment