ODBC Error [22003] VALRNG: Column or parameter 3: Value out of range

[coatsy35]

Hi all,

I'm getting the following error on one of the tables in my database connected via ODBC.  Does anyone know what I can do about this? the table explores fine in a ODBC database viewer and I cna't see a wrong character in the column, especially not column 3.

Is there a way I can use adavanced editor to exclude that column and force it to display the table? here is my syntax generated in the advanced editor. Any help appreciated

let
    Source = Odbc.DataSource("dsn=Gold001", [HierarchicalNavigation=true]),
    PUBLIC_Schema = Source{[Name="PUBLIC",Kind="Schema"]}[Data],
    INVOICE_TYPES_Table = PUBLIC_Schema{[Name="INVOICE_TYPES",Kind="Table"]}[Data]
in
    INVOICE_TYPES_Table

[lbendlin]

What's the column type?

[coatsy35]

it's a TINYINT if it is column 3. the values in it are between -14 and 9 in the current table. It's information out of an ERP system so I can't alter the database or table

[lbendlin]

Is that SQL Server?  On some systems TinyInt cannot store negative numbers, only 0..255 .

Power Query however seems to be just fine with it

It correctly errors out for 128

[coatsy35]

It's a Flat file database for a business management system written in COBOL on the synergy/de platform by Synergy. I cannot alter anything to do with the datebase. The vendor supplies the drivers. They only use it in excel Legacy mode query which reads it and loads fine. However power query and power BI do not like that table and give the error above. All the other 400 tables in the database load fine, which is annoying to say the least that this table I need for a lookup doesn't work

[v-xinruzhu-msft]

HI,

Thanks for the solution lbendlin offered, and i want to offer some more information for user to refer to.

hello @coatsy35 , based on your descriotion, the odbc connector provides SQL queries in advanced options, which you can use to select the columns you need by using sql queries.

And you can refer to the following link.

Import data from a database using native database query - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

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