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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
danextian
Super User
Super User

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!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
16 REPLIES 16
QueryWhiz
Post Patron
Post Patron

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:

 

SALESFORCE-1.png

 

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:

 

SALESFORCE-2.png

 

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. 

 

SELECT_DESTINATION_NEW.png

Anonymous
Not applicable

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

Anonymous
Not applicable

This works

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

Anonymous
Not applicable

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
Not applicable

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
Super User
Super User

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!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
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

This method worked for me. Thanks for sharing!

This fixed it for me too, thanks

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
v-jiascu-msft
Microsoft Employee
Microsoft Employee

@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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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