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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors