cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danextian
Resident Rockstar
Resident Rockstar

DataSource.Error: invalid query locator when expanding a column

Hi All,

 

I'm trying to expanding a column from two merged queries. Both queries are from two separate salesforce objects. The second one only has one column called Materials.  However, when I try to expanding this column, I get this error. 

 

DataSource.Error: invalid query locator
Details:
    List

 

 

I could load the entire Materials table  but it would be unwise since I don't need all the rows - just those with a match from the first query.  I need just less than 2k out of over 100k rows. 

 

Below is query code: 

 

let
Source = Salesforce.Data(),
Product2 = Source{[Name="Product2"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Product2,{"Name", "Description", "IsActive", "Family", "Product_Model__c", "Brand__c", "Effective_Date__c", "End_Date__c", "LX_Product_Line1__c", "Parent_Product__c", "Part_Number__c", "Product_Category__c", "Profit_Center__c", "Series__c", "Status__c", "BigMachines__Part_Number__c", "Part_Type__c", "LX_Originating_Division__c", "LX_Product_Series1__c", "LX_Product_Family__c", "LX_Product_Model_Model__c", "PM_User_Segment__c", "Product_Group__c", "Product_Type__c"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"BigMachines__Part_Number__c", "DD Material Number"}, {"Part_Number__c", "Material Number"}, {"Name", "Desc"}, {"Description", "Material Description"}, {"Brand__c", "Brand Name"}, {"LX_Product_Line1__c", "Product Line"}, {"Series__c", "Product Series"}, {"Status__c", "Product Status"}, {"PM_User_Segment__c", "Product User Segment"}, {"LX_Originating_Division__c", "Product Division"}, {"LX_Product_Series1__c", "Series_d"}, {"LX_Product_Model_Model__c", "Product Model"}, {"Product_Group__c", "Product Group"}, {"Product_Type__c", "Product Type"}, {"LX_Product_Family__c", "Product Family"}, {"Product_Category__c", "Product Category"}, {"Profit_Center__c", "Profit Center"}, {"Part_Type__c", "Part Type"}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns",{"Family", "Brand Name", "Product Line", "Material Number", "Product Category", "Profit Center", "Product Series", "Product Status", "DD Material Number", "Part Type", "Product Division", "Product Family", "Product Model", "Product User Segment", "Product Group", "Product Type"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns1",{{"Material Number", Text.Trim}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text",{"Material Number"},#"Unique Materials",{"Material"},"NewColumn",JoinKind.LeftOuter)
in
#"Merged Queries"
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
14 REPLIES 14
daveb501commons
New Member

In PQ query - refresh preview on the specific query with the issue fixed the problem.

Anonymous
Not applicable

This thread has provided me with lots of insight into this problem, but I stumbled upon a fix that's related to some of the one's mentioned here. 

For instance, let's say I have a table with 100 rows and I encounter this issue. I found a fix by Remove Bottom 1 Row and found another fix by Keep Top 99 Rows.

I don't have a screen shot, but i recall seeing an error message with a SQL query with missing arguments. My hypothesis is that when querying related tables, it can error when it hits the end of a list/array. As OP mentioned, "As shown on the screenshot below, the remove errors features doesn't work." So my idea in a nutshell was to manuall remove the row that was giving me an error, so to speak. 

jdballard30
Helper II
Helper II

All I had to do was refresh the preview of my data and the error went away.

Here's an excerpt from https://help.salesforce.com/articleView?id=000004410&language=en_US&type=1 which sheds some light:

Each Salesforce user can have up to 10 query cursors open at a time. This is a hard coded limit that cannot be increased. If 10 query locator cursors are opened when a client application attempts to open a new one, then the oldest cursor is released. If a call is attempted to an expired or released cursor, salesforce.com will return the INVALID_QUERY_LOCATOR error. To avoid this error, ensure that the client code is not holding open more than 10 query locator cursors.  If multiple processes are running using the same salesforce.com user, make sure that their combined query locator cursor count remains at 10 or below.

That's really good info and probably explains why several people are reporting solutions which seem unrelated, included the simple "I just refreshed again and it went away". Each of these "solutions" must have somehow reduced the query locator curser count below 10. Now, if we could figure out a more consistent way to do that!

I have been vexed by this off and on for about as long as this thread has been going on. This is great advice:
    "To avoid this error, ensure that the client code is not holding open more than 10 query locator cursors." 
But HOW do I do that? I'm not sure but will post again if I figure it out!

 

UPDATE: the easiest way to resolve this is to step through each query and "Refresh Preview" for the queries one by one (not "Refresh all").

 

If this solves your issue, please mark as a solution. thanks!

GCL
Frequent Visitor

Dear,

 

indeed, very interesting point.


I'll discuss this "10 query locator cursors" topic with a Salesforce expert.
I'll come back to this message as soon as I receive a relevant feedback.

Thx


Best regards,
Gaston

fhendrick
Advocate I
Advocate I

Hi all,

 

I'm facing the same issue with Salesforce objects. Source query parameters are already defined as @GCL said with "https://...".

 

Last column are tables and values salesforce object, when I'm trying to Expand Column (not to have "Table" or "Value" in cells but real column value), it says exactly the same error.

 

I founded a workaround to avoid this issue : to limit the size of the preview as said bi @v-jiascu-msft

 

Then I'm looking for a way to let it go for all lines without size limitation. It's like to launch a too big query to Salesforce causes this issue.

smitty
Resolver II
Resolver II

Encountered this error using SalesForce Opportunity Line Item table.  Created a copy of that table, filtered the rows, renamed columns and grouped the data.  Then attempted merge using an imported ForeCast table.  The workaround is to change the JoinKind from LeftOuter to Inner

= Table.NestedJoin(#"OpportunityLineItemGrouped",{"FYQ", "Region", "SubRegion", "Product Line"},Target,{"FYQ", "Region", "SubRegion", "Product Line"},"Target",JoinKind.Inner)

 

Also, found this link in SalesForce help

https://help.salesforce.com/articleView?id=000004410&type=1

 

HTH,

Smitty

 

 

danextian
Resident Rockstar
Resident Rockstar

It seesm that this is an issue with salesforce objects that PBI desktop cant properly handle. As shown on the screenshot below, the remove errors features doesn't work.

 

removeerrors not working.png

 

The error is still the same:

 

DataSource.Error: invalid query locator
Details:
    List
Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
GCL
Frequent Visitor

Dear,

 

I had the same "invalid query locator" issue with Salesforce.com related data.

After selecting the following menu path "Power BI Desktop - Home - Edit queries - Advanced Editor", I changed the "source" parameters :

 

Situation before :

let
    Source = Salesforce.Data(),
    Opportunity1 = Source{[Name="Opportunity"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Opportunity1, each [Dep_Code] = "PER")
in
    #"Filtered Rows"

 

Situation afterwards:

let
    Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties=true]),
    Opportunity1 = Source{[Name="Opportunity"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Opportunity1, each ([Dep_Code] = "PER"))
in
    #"Filtered Rows"

 

That means:  the issue in my scenario has been solved by approaching the Salesforce data via my corporate salesforce account + applying correct source parameters.

 

Cheers!

Gaston

gsndeepak
Regular Visitor

This method worked for me. Thanks for sharing!

This fixed it for me too, thanks

v-jiascu-msft
Microsoft
Microsoft

Hi @danextian,

 

Could you please mark the proper answer or share your answers if it's convenient for you? That will be a help to others.

 

Best Regards!
Dale

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

hi @v-jiascu-msft,

 

I still haven't found an answer to this but  hopefully I'm g etting  there. 

 

I did try other wasy before but they did not work. I am assuming one or more of the records in the table contains in a list instead of an actual value thus the error.

Did I answer your question? Mark my post as a solution!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
v-jiascu-msft
Microsoft
Microsoft

@danextian

 

Hi,

 

I can’t reproduce your issue. Please check these things below to do a trouble shooting.

  1. Upgrade PBI to the latest version. (2.46.4732.721 64-bit (May 2017))
  2. Try to reduce the fields. Reference here: http://community.powerbi.com/t5/Desktop/Query-Salesforce-Data-Error-Query-too-complicated/m-p/61053/...
  3. Do “get data, merge queries” first, then do the other actions such as renaming columns etc.
  4. Try to use “Merger queries as New”.

  

Best Regards!

Dale

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!