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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    thank you, i hope you've seen my reply prior to this one where i tried your suggestion. unfortunately looks like odbc is my only option for now, but this requires a gateway so we can schedule our refresh.

     

    here are the two columns i was referring to which i suspect is causing the error. this is the table in redshift and literally all columns are in varchar(2000) data type:

     

     

    i am trying to force power bi to read them as strings so i added a "#" prefix to the date column. for the ID column with 9-digit numeric values, i only replaced the nulls with "#". unfortunately this sad attempt as a workaround still did not work

     

    it's just so weird that the table loads via ODBC. i think this is poor design/functionality on power bi's part since i'm confident there is no issue with redshift

     

     

    still hoping anyone has encountered this issue and has a workaround...