DataSource.Error: invalid query locator when expanding a column

[danextian]

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"

[QueryWhiz]

Hi @danextian were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

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

[Anonymous]

This works

[Anonymous]

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]

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

[jvaught]

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]

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

[Anonymous]

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.

[Anonymous]

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]

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.

The error is still the same:

DataSource.Error: invalid query locator
Details:
    List

[GCL]

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]

This method worked for me. Thanks for sharing!

[lolouk44]

This fixed it for me too, thanks

[v-jiascu-msft]

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

[danextian]

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.

[v-jiascu-msft]

@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