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

[Anonymous]

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 the table doesn't show any issues. i tried loading the same table via ODBC instead of redshift import, literally just select * and it works

sorry I had to cover information, but i'm at a dead end here since I've exhausted everything that I can check and the problem looks weird. I am pretty sure this is a power bi issue because there are no problems with table in the database

hope someone can answer, thank you!

[pcylupa]

I encountered the same issue. In our case, the problem was with the construction of the view on the Redshift side.

The creator of the view added an empty column as a placeholder for later development. The view looked like this

create or replace view nulltest1_v as 
select
…,
null as col3
from ...
with no schema binding

I think the error stems from the fact that on this type of view, Power BI connector doesn't know the type (varchar, integer etc.) of the added column, which causes the error.

[kaixtae]

HI! Has there been a known resolution for this? I am encountering the same issue.

[smartin_ec]

The fix for me was determining which fields had no values -- all values were null.  And then changing the null value to a blank did not work - had to put in a text value of 'NA' and then use power BI replace value to change the "NA" back to a blank/null.  Seems that if no data (or all blanks) is in a field with the redshift connector it defaults it to an integer value ... Boo!!

[v-kelly-msft]

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]

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...

[Anonymous]

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:

[amitchandak]

@Anonymous , Refer :https://community.powerbi.com/t5/Desktop/Error-quot-Cannot-convert-value-xxxx-of-type-Text-to-type/m-p/967516

or

Try to change that column as text and check if it works.

This is not a power bi solution but will give an idea

https://medium.com/@sethsubr/handling-postgresql-invalid-input-syntax-for-integer-null-while-type-casting-a-column-declared-5debfb3e864b