Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

ERROR [22P02] [SQLState 22P02] ERROR: invalid input syntax for integer: ""

Hi all, Anybody familiar with this error message and knows how to fix this?   data source is a table from Amazon Redshift, and the table is fine in redshift. we can do our queries and ...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Anonymous ，

 

After checking the doc,  for Amazon Redshift,not all sorts of transformations and filters to the data are supported.

Once you make selections from the Navigator window, you can either Load or Edit the data.

  • If you choose to Load data, you'll be prompted to use either Import or DirectQuery mode to load the data. For more information, check out this article that explains DirectQuery.
  • If you select to Edit the data, Query Editor appears where you can apply all sorts of transformations and filters to the data, many of which are applied to the underlying Amazon Redshift database itself (if supported).

So you'd better choose another connector such as ODBC if you wanna filter the data before loading to power bi desktop.

Here is the reference.

https://docs.microsoft.com/en-us/power-bi/desktop-connect-redshift

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

thanks for the suggestions! i swear all columns in the database are strings / varchar, i also thought it was a data type issue since integers can't be empty or null. there actually is a date column and an ID column composed of 9-digit numeric values, but both have nulls so i had to set the data type as varchar in the database.

 

i thought pbi must be automatically trying to set the data type as date or integer, and returns the error while loading once it detects the nulls. unfortunately i couldn't get to the "transform data" part through the navigator. preview of the table won't even show up, the same error message appears in the navigator: