{"id":3844,"date":"2022-03-10T11:17:53","date_gmt":"2022-03-10T19:17:53","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3844"},"modified":"2022-03-10T11:17:55","modified_gmt":"2022-03-10T19:17:55","slug":"how-to-fix-data-type-errors-in-power-bi","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3844","title":{"rendered":"How to Fix “Data type errors” in Power BI"},"content":{"rendered":"\n
Sometimes, when you import data into Power BI, the columns appear blank. This situation happens because of an error in interpreting the data type in Power BI. The resolution to this error is unique to the data source. For instance, if you are importing data from SQL Server and see blank columns, you could try to convert to the correct data type in the query.<\/p>\n\n\n\n
Instead of using this query:<\/p>\n\n\n\n
SELECT CustomerPostalCode FROM Sales.Customers<\/code><\/p>\n\n\n\n
Use this query:<\/p>\n\n\n\n
SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers<\/code><\/p>\n\n\n\n
By specifying the correct type at the data source, you eliminate many of these common data source errors.<\/p>\n\n\n\n
Read below for more detail if needed.<\/p>\n\n\n\n
Introduction to SQL Server CAST()<\/code> function<\/h2>\n\n\n\n
Let\u2019s see the following query:<\/p>\n\n\n\n
SELECT 1 + '1' AS result;<\/code>Code language: PHP (php)<\/small><\/pre>\n\n\n\n
In this statement, SQL Server implicitly converts the character string '1'<\/code> to the number 1<\/code>.<\/p>\n\n\n\n
When you use two values with different data types, SQL Server will try to convert the lower data type to the higher one before it can process the calculation. This is known as an implicit conversion in SQL Server.<\/p>\n\n\n\n
In contrast to implicit conversions, we have explicit conversions where you call the CAST()<\/code> function to explicitly convert a value of one type to another:<\/p>\n\n\n\n
SELECT 1 + CAST(1 AS INT) result;<\/code>Code language: PHP (php)<\/small><\/pre>\n\n\n\n
The syntax of the CAST()<\/code> function is as follows:<\/p>\n\n\n\n
expression<\/code> can be a literal value or a valid expression of any type that will be converted.<\/li>
target_type<\/code> is the target data type to which you want to convert the expression. It includes INT<\/code>, BIT<\/code>, SQL_VARIANT<\/code>, etc. Note that it cannot be an alias data type.<\/li>
length<\/code> is an optional integer that specifies the length of the target type. The length<\/code> defaults to 30.<\/li><\/ul>\n\n\n\n
The CAST()<\/code> function returns the expression converted to the target data type.<\/p>\n\n\n\n
SQL Server CAST()<\/code> function examples<\/h2>\n\n\n\n
Let\u2019s take some examples of using the CAST()<\/code> function.<\/p>\n\n\n\n
A) Using the CAST()<\/code> function to convert a decimal to an integer example<\/h3>\n\n\n\n
This example uses the CAST()<\/code> function to convert the decimal number 5.95<\/code> to an integer:<\/p>\n\n\n\n
SELECT CAST(5.95 AS INT) result;<\/code>Code language: CSS (css)<\/small><\/pre>\n\n\n\n
B) Using the CAST()<\/code> function to convert a decimal to another decimal with different length<\/h3>\n\n\n\n
The following example uses the CAST()<\/code> function to convert the decimal number 5.95 to another decimal number with the zero scale:<\/p>\n\n\n\n
SELECT CAST(5.95 AS DEC(3,0)) result;<\/code>Code language: CSS (css)<\/small><\/pre>\n\n\n\n
The output is as follows:<\/p>\n\n\n\n
result\n-------\n6<\/code><\/pre>\n\n\n\n
When you convert a value of the data types in different places, SQL Server will return a truncated result or a rounded value based on the following rules:<\/p>\n\n\n\n
D) Using CAST()<\/code> function with arithmetic operators<\/h3>\n\n\n\n
We will use the sales.orders<\/code> and sales.order_items<\/code> tables from the sample database for the demonstration:<\/p>\n\n\n\n<\/figure>\n\n\n\n
The following statement uses the CAST()<\/code> function to convert the monthly sales in 2017 to integer values.<\/p>\n\n\n\n
SELECT \n MONTH(order_date) month, \n CAST(SUM(quantity * list_price * (1 - discount)) AS INT) amount\nFROM sales.orders o\n INNER JOIN sales.order_items i ON o.order_id = i.order_id\nWHERE \n YEAR(order_date) = 2017\nGROUP BY \n MONTH(order_date)\nORDER BY \n month;<\/code>Code language: PHP (php)<\/small><\/pre>\n\n\n\n
The following picture shows the output:<\/p>\n\n\n\n<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server CAST()<\/code> function to convert a value from one type to another.<\/p>\n\n\n\n
![\"Sample](\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/orders-order_items.png\")
<\/figure>\n\n\n\n![\"SQL](\"https:\/\/www.sqlservertutorial.net\/wp-content\/uploads\/SQL-Server-CAST-Function-example.png\")
<\/figure>\n\n\n\n