cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danextian New Contributor
New Contributor

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"
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
13 REPLIES 13
v-jiascu-msft Super Contributor
Super Contributor

Re: DataSource.Error: invalid query locator when expanding a column

@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.
v-jiascu-msft Super Contributor
Super Contributor

Re: DataSource.Error: invalid query locator when expanding a column

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.
danextian New Contributor
New Contributor

Re: DataSource.Error: invalid query locator when expanding a column

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.

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
danextian New Contributor
New Contributor

Re: DataSource.Error: invalid query locator when expanding a column

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
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
smitty Frequent Visitor
Frequent Visitor

Re: DataSource.Error: invalid query locator when expanding a column

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

 

 

GCL Frequent Visitor
Frequent Visitor

Re: DataSource.Error: invalid query locator when expanding a column

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

lolouk44 Frequent Visitor
Frequent Visitor

Re: DataSource.Error: invalid query locator when expanding a column

This fixed it for me too, thanks

Highlighted
fhendrick Frequent Visitor
Frequent Visitor

Re: DataSource.Error: invalid query locator when expanding a column

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.

jdballard30 Regular Visitor
Regular Visitor

Re: DataSource.Error: invalid query locator when expanding a column

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 141 members 1,604 guests
Please welcome our newest community members: