ODBC error: numeric overflow or string truncation. Expected length 31, actual 63

[StefaniaVargas]

Good day,

Im having a error in power Query after the IT department connected Power BI to ODBC database. The error is the following:

ODBC error numeric overflow or string truncation
expected lenght 31, actual 63
details:
Datasourcekind= Odbc
DatasourcePath=dsn= VARIO
OdbcErrors =[Table]

I've tried to solve the truncation error from power query editor updating the M Code as follow but the problem persist

let
Quelle = Odbc.DataSource("dsn=VARIO", [HierarchicalNavigation=true]), REB_Table = Quelle{[Name="REB",Kind="Table"]}[Data],
// Get the list of text columns
TextColumns = List.Select( Table.ColumnNames(REB_Table), each Value.Type(Table.Column(REB_Table, _)) = type text ),
// Truncate only text columns to 31 characters
TruncatedTable = Table.TransformColumns( REB_Table, List.Transform( TextColumns, each {_, each Text.Start(_, 31), type text}
)
)
in
TruncatedTable


Would appreciated any idea of what the solution might be, thank you in advanced.

Many Thanks
Kind Regards 

[v-ssriganesh · Accepted solution]

Hi @StefaniaVargas,
Thanks for posting your query in Microsoft fabric community forum.

After thoroughly reviewing the details you provided, this error occurs when the actual data length exceeds the expected column length defined in the ODBC driver or database. Here are some steps you can try to resolve it:

• The error message indicates that a column expects 31 characters, but your data has 63 characters. Can you verify with your IT team if the database schema has the correct column size?
• Your method of truncating text columns is useful, but Power Query might still read the original column length. Try explicitly setting the column type.
• Some ODBC drivers impose default character length limitations. Ask your IT team to review and increase the string column length in the DSN settings. Ensure you’re using the latest ODBC driver for your database.
  
  

If this helps, then please “Accept it as a solution” and dropping a "Kudos" so other members can find it more easily.
Thank you.

[v-ssriganesh]

Hi @StefaniaVargas,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @StefaniaVargas,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin · Accepted solution]

That error comes from the source. Check the column types in the source.

[v-ssriganesh · Accepted solution]

Hi @StefaniaVargas,
Thanks for posting your query in Microsoft fabric community forum.

After thoroughly reviewing the details you provided, this error occurs when the actual data length exceeds the expected column length defined in the ODBC driver or database. Here are some steps you can try to resolve it:

• The error message indicates that a column expects 31 characters, but your data has 63 characters. Can you verify with your IT team if the database schema has the correct column size?
• Your method of truncating text columns is useful, but Power Query might still read the original column length. Try explicitly setting the column type.
• Some ODBC drivers impose default character length limitations. Ask your IT team to review and increase the string column length in the DSN settings. Ensure you’re using the latest ODBC driver for your database.
  
  

If this helps, then please “Accept it as a solution” and dropping a "Kudos" so other members can find it more easily.
Thank you.

[v-ssriganesh]

Hi @StefaniaVargas,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.