Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
amitksingh2003
Regular Visitor

Restrictions on size/number of records in Teradata SQL statement in advanced editor

Hi friends, I recently started learning about Power BI and trying to use to automate a task which takes huge manual effort.

 

I am done with most part of it except hitting an error while trying to retrieve some data from teradata database.

 

I have a list of payment reference numbers stored in a table named "BI_Data" and column name "Payment Reference Numbers". This column has some 37,000 odd values. I need to retrieve additional details from Teradata with reference to these payment reference numbers.

 

I am using a variable "PayRefNum" in advanced editor to get the list from table and format it per sql statement requirement for delimiter.

 

let
    PayRefNum= "'246532387" & Text.Combine(List.Distinct(BI_Data[Payment Reference Number]),"','"),
    Source = Teradata.Database("SERVER", [Query="SELECT FP.PRO_NBR,FP.SCAC_CODE,FP.WM_TRANS_MODE_CD,FP.CARRIER_NAME,FP.SHIP_DATE,FP.SHIPPER_NAME,FP.SHIPPER_CITY_NAME,FP.SHIPPER_STATE_CODE,FP.SHIPPER_POSTAL_CODE,FP.CNSGN_NAME,FP.CNSGN_CITY_NAME,FP.DEST_STORE_NBR,FP.FRT_PYMT_SEQ_NBR,FP.TOTAL_PAID_AMT,FP.PAID_DATE FROM US_WM_LDSS_VM.FREIGHT_PAYMENT FP WHERE FP.FRT_PYMT_SEQ_NBR IN (" & PayRefNum & "')", CreateNavigationProperties=false])
in
    Source


The query works fine when I pass limited number of values for payment reference numbers. However, I get error saying could not convert string to numericals when trying to pass all 37,000 odd payment reference numbers to the query.


When troubleshooting, I can see all the payment reference numbers are correctly loaded to the variable "PayRefNum". However, the values seems to get truncated in query statement.

 

PBI Error2.JPGPBI Error.JPG

 


I wanted to understand if this is because of any limit/restrictions on size or number of records in advanced editor and what is the work around it. My teradata doesn't have this size restriction as I am able to retrieve data successfully directly from teradata for same set of records.

1 ACCEPTED SOLUTION
amitksingh2003
Regular Visitor

Hi @v-frfei-msft. Thanks a lot for the quick response. 

As you pointed out, the limit of 32,766 seems to be the reason I am getting a truncated view in query run window. However, fortunately, that was not the reason for error in actual running of query. Seems the limit was impacting just the view.

 

As it turns out, there was a "#" character in my query which was giving this error. I added few validations to check data sanity before query and now it's running fine. Thanks again for your respnose.

View solution in original post

2 REPLIES 2
amitksingh2003
Regular Visitor

Hi @v-frfei-msft. Thanks a lot for the quick response. 

As you pointed out, the limit of 32,766 seems to be the reason I am getting a truncated view in query run window. However, fortunately, that was not the reason for error in actual running of query. Seems the limit was impacting just the view.

 

As it turns out, there was a "#" character in my query which was giving this error. I added few validations to check data sanity before query and now it's running fine. Thanks again for your respnose.

v-frfei-msft
Community Support
Community Support

Hi @amitksingh2003

 

As the blog, The maximum length of a text value that the Power Query engine can load into a single cell in a table in a dataset is 32766 characters – any more than that and the text will be silently truncated.

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors