How to Fix “Data type errors” in Power BI

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.

Instead of using this query:

SELECT CustomerPostalCode FROM Sales.Customers

Use this query:

SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers

By specifying the correct type at the data source, you eliminate many of these common data source errors.

Read below for more detail if needed.

Introduction to SQL Server CAST() function

Let’s see the following query:

SELECT 1 + '1' AS result;Code language: PHP (php)

It returns 2 as a number:

result
-----------
2

(1 row affected)

In this statement, SQL Server implicitly converts the character string '1' to the number 1.

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.

In contrast to implicit conversions, we have explicit conversions where you call the CAST() function to explicitly convert a value of one type to another:

SELECT 1 + CAST(1 AS INT) result;Code language: PHP (php)

The syntax of the CAST() function is as follows:

CAST ( expression AS target_type [ ( length ) ] )  Code language: CSS (css)

In this syntax:

• expression can be a literal value or a valid expression of any type that will be converted.
• target_type is the target data type to which you want to convert the expression. It includes INT, BIT, SQL_VARIANT, etc. Note that it cannot be an alias data type.
• length is an optional integer that specifies the length of the target type. The length defaults to 30.

The CAST() function returns the expression converted to the target data type.

SQL Server CAST() function examples

Let’s take some examples of using the CAST() function.

A) Using the CAST() function to convert a decimal to an integer example

This example uses the CAST() function to convert the decimal number 5.95 to an integer:

SELECT CAST(5.95 AS INT) result;Code language: CSS (css)

Here is the output:

result
-----------
5

(1 row affected)

B) Using the CAST() function to convert a decimal to another decimal with different length

The following example uses the CAST() function to convert the decimal number 5.95 to another decimal number with the zero scale:

SELECT CAST(5.95 AS DEC(3,0)) result;Code language: CSS (css)

The output is as follows:

result
-------
6

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:

From Data Type	To Data Type	Behavior
numeric	numeric	Round
numeric	int	Truncate
numeric	money	Round
money	int	Round
money	numeric	Round
float	int	Truncate
float	numeric	Round
float	datetime	Round
datetime	int	Round

C) Using the CAST() function to convert a string to a datetime value example

This example uses the CAST() function to convert the string '2019-03-14' to a DateTime:

SELECT 
    CAST('2019-03-14' AS DATETIME) result;Code language: PHP (php)

The output is:

result
-----------------------
2019-03-14 00:00:00.000

(1 row affected)Code language: CSS (css)

D) Using CAST() function with arithmetic operators

We will use the sales.orders and sales.order_items tables from the sample database for the demonstration:

The following statement uses the CAST() function to convert the monthly sales in 2017 to integer values.

SELECT 
    MONTH(order_date) month, 
    CAST(SUM(quantity * list_price * (1 - discount)) AS INT) amount
FROM sales.orders o
    INNER JOIN sales.order_items i ON o.order_id = i.order_id
WHERE 
    YEAR(order_date) = 2017
GROUP BY 
    MONTH(order_date)
ORDER BY 
    month;Code language: PHP (php)

The following picture shows the output:

In this tutorial, you have learned how to use the SQL Server CAST() function to convert a value from one type to another.

Ref: https://www.sqlservertutorial.net/sql-server-system-functions/sql-server-cast-function/