Error when loading data into a visual

[kumar9999]

Hello,

We are trying to connect power bi to AWS Redshift through the native connector. When we try to load a view using Direct Query Method, the columns which are of datatype "Time" could not be loaded into visuals.

I get the below error, when i try to load them into a table visual. 

The format is of "Time" what power bi shows me on the desktop and the data is in the below format in the Power Query Preview.

Can someone help me with this issue? and what could be the reason we cant load these values? Greatly Appreciated. Thx

[Anonymous]

Hi, @kumar9999 

I'm happy to answer your questions. According to the error message: When loading AWS Redshift, an error occurred while trying to execute a query. Specifically, the pg_catalog_date_add function was triggered. This function is a function of the PostgreSQL database that is used to add a specified time interval to the date. This function usually requires passing two parameters, a date and a time interval. According to the error message, the first parameter of the function is marked as unknown, which means that the parameter passed to the function when executing the query is of incorrect type or format.

You can troubleshoot problems from the following aspects:

1. Check your query statement to ensure that it is passing a date type parameter. This parameter should be recognized and processed by PostgreSQL.
2. Database version is incompatible: AWS Redshift database version is incompatible with Power Query

The links below are the same posts as your error report. You can use them to learn more about this error:

https://stackoverflow.com/questions/57472899/amazon-redshift-dateadd-in-case-statement

https://github.com/fivetran/dbt_stripe/issues/22

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kumar9999]

Thank you for the response. 

In power bi, im not writing any sql statement. Im using the native AWS Redshift connector and selecting "Direct Query" when reading the data. I can see the data with "Time" datatype in the power query editor under data preview, but when i try to use the column in the table visual, thats when i get this error."

should i be doing anything in power bi? or something should be changed at the redshift database side when building the view?

[Anonymous]

Hi, @kumar9999 

Thank you very much for your reply, you can try the following methods:
1. Check the DAX you are using in visual to see if there is a parameter of double precision type. If there is, you can try to explicitly cast it to another type, such as whole number.
2. Check that the column type used by your DAX is consistent with the type of the corresponding column in Amazon Redshift.
3. Update Power BI and Amazon Redshift drivers: Make sure you are using the latest versions of Power BI and Amazon Redshift drivers. Sometimes, updating the driver can solve problems related to database connections.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @kumar9999 ,

can you attach the pbis file so we could check it