Power Bi Query Error using Quickbooks Connector - Truncation

[BRAGAIDAN]

Receiving the following error when attempting load Invoice_Sales_Item_Line table using Microsoft's Quickbooks Online Connector.

Researcdhed thoroughly and have yet to come up with a solution.

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][DSI] (20039) Cannot store "QuickBooks"."QuickBooks"."Invoice_Sales_Item_Line"."Line_Description" value in temporary table without truncation. (Column metadata implied a maximum of 1024 bytes, while provided value is 1205 bytes)
Details:
DataSourceKind=QuickBooksOnline
DataSourcePath=QuickBooksOnline
OdbcErrors=[Table]

This error appears to be due to the Line_Description field having too many characters to load.  Any assistance in dealing with truncation would be appreciated. 

[v-tsaipranay]

Hi @BRAGAIDAN ,

Could you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @BRAGAIDAN ,

Thank you for reaching out to the Microsoft fabric community forum  regarding the issue you’re experiencing with the QuickBooks Online Connector in Power BI.

The error you’re encountering, which states that the Line_Description exceeds the maximum character limit during the table expansion, indicates that Power BI is unable to store the data without truncating it. This typically occurs because the data being pulled exceeds the configured limits.

To address the truncation issue efficiently, start by retrieving and expanding simpler or minimal columns. After confirming the structure and functionality of these fields, you can then explore solutions for the Line_Description error more effectively.

Here are the troubleshooting steps to address the issue:

• Firstly, rather than directly expanding the table, consider viewing the schema or metadata of the data beforehand. This can be achieved by loading a small sample of the data or previewing its structure using the appropriate methods for your data connector.
• When utilizing the QuickBooks Online connector, data is typically retrieved through a single query. To minimize issues, consider using any available options to limit or filter the data being pulled. For instance, if you have control over the query, you could reduce the dataset by applying certain conditions (if supported by the connector) to exclude records with lengthy descriptions from being loaded initially.
• If your connector supports writing more advanced queries or API calls, focus only on the specific fields you need, temporarily excluding the Line_Description. This approach allows you to expand a smaller subset of data initially without causing truncation.
• Try to load the Invoice_Sales_Item_Line table with only the essential fields that don’t include Line_Description first. This way, you isolate the table's primary structure without triggering the truncation error.

For more information please refer below documentation:

Power Query QuickBooks Online connector - Power Query | Microsoft Learn

If the issue persists after troubleshooting above steps, I suggest you raise a support ticket so they can assist in addressing the issue, please refer below link on how to raise a contact support or support ticket.

https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket

Since this issue may also be related to how QuickBooks handles data exports, reaching out to QuickBooks support could provide additional insights or updates regarding any known issues with their API or data structure.

I hope these suggestions help you overcome the truncation issue while working with QuickBooks Online data in Power BI. If you have any further questions or require additional assistance, please feel free to reach out again.

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

Thank you. 

[v-tsaipranay]

Hello @BRAGAIDAN ,

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-tsaipranay]

Hello @BRAGAIDAN ,

I wanted to follow up on our previous suggestions regardingPower Bi Query Error using Quickbooks Connector - Truncation. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-tsaipranay]

Hi @BRAGAIDAN ,

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

Thank you.

[jvaught]

There is no obvious way to do what you have suggesting, since this error happens on the very first step.

Here is the query.
let
Source = QuickBooksOnline.Tables(null),
Invoice_Sales_Item_Line_Table = Source{[Name="Invoice_Sales_Item_Line",Kind="Table"]}[Data]
in
Invoice_Sales_Item_Line_Table

[jvaught]

There is no obvious way to do what you have suggesting, since this error happens on the very first step.

Here is the query.
let
Source = QuickBooksOnline.Tables(null),
Invoice_Sales_Item_Line_Table = Source{[Name="Invoice_Sales_Item_Line",Kind="Table"]}[Data]
in
Invoice_Sales_Item_Line_Table

[BRAGAIDAN]

Thanks for the response.  Unfortunately the error message occurs when expanding the table so the field doesn't exist in the query editor yet.  I would need a way to truncate prior to expanding the table.

[rohit1991]

To handle truncation in Power BI:

1. Truncate Data:

   • In Power Query, select Line_Description, go to Transform > Extract > Text Before Length, and set the length to 1024.

2. Remove Column:

   • If Line_Description isn't required, select the column, right-click, and choose Remove.

3. Split Column:

   • In Power Query, select Line_Description, go to Transform > Split Column > By Number of Characters, and set the size to 1024.

[jvaught]

We can't transform the column, because the table won't load in the first place. Here is the query.
let
   Source = QuickBooksOnline.Tables(null),
   Invoice_Sales_Item_Line_Table = Source{[Name="Invoice_Sales_Item_Line",Kind="Table"]}[Data]
in
   Invoice_Sales_Item_Line_Table

It fails on the "Invoice_Sales_Item_Line_Table" step. Do you know of any way to truncate it BEFORE it loads?

Also, this is the first time we've experienced this error. Because of that, I was able to manually go though all invoices which have been created since the last time this report was refreshed and none of them have line descriptions that seem too long. The longest one was only 498 characters, or 588 bytes when encoded in UTF-8 (I have no idea if QB uses that, but in any case, it seems like it's nowhere near 1024).