Forum Discussion
salesforce object connector - Lookup relationship support ?
- 9 years ago
OK, after playing around with this some more I found a solution and it's actually quite simple and functional. I just couldn't find any documentation on the subject.
For reference here is how you can query on lookup relationship fields using the Salesforce Object connector:
1 - When picking Salesforce Object connector, on the screen where you select the object you just check the option at the bottom of the window entitled "Include Relationship Columns"
2 - Click on "Choose Columns" and scroll to the bottom where all of the lookup fields are located. They will end in __r, such as Market__r. Select the lookup relationship you'd like to use
3 - From the preview window, click on the hierarchy looking button on the column header for the lookup relationship. This will pop out a window where you can select the relationship fields in question, such as Market__r.Name
Here's a screenshot for reference and the Dax it generates for reference:
Dax from Advanced Editor:
let
Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties = true]),
Account1 = Source{[Name="Account"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Account1,{"Name", "RecordTypeId", "Market__r"}),
#"Expanded Market__r1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Market__r", {"Name"}, {"Market__r.Name"}),
#"Expanded Market__r" = Table.ExpandRecordColumn(#"Expanded Market__r1", "Market__r", {"Id", "Name", "Parent_Market__c", "Geocode__c", "Geocode_Address__c", "Parent_Market__r"}, {"Market__r.Id", "Market__r.Name", "Market__r.Parent_Market__c", "Market__r.Geocode__c", "Market__r.Geocode_Address__c", "Market__r.Parent_Market__r"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Market__r", each ([RecordTypeId] = "012130000018FFPAA2"))
in
#"Filtered Rows"
Hi ivanoharris,
I am not able to found any official Power BI blog or salesforce blog stating that there is a limitation about lookup relationship. Lookup relationship should be supported in the connector, could you please post the error message you get?
Thanks,
Lydia Zhang
OK, after playing around with this some more I found a solution and it's actually quite simple and functional. I just couldn't find any documentation on the subject.
For reference here is how you can query on lookup relationship fields using the Salesforce Object connector:
1 - When picking Salesforce Object connector, on the screen where you select the object you just check the option at the bottom of the window entitled "Include Relationship Columns"
2 - Click on "Choose Columns" and scroll to the bottom where all of the lookup fields are located. They will end in __r, such as Market__r. Select the lookup relationship you'd like to use
3 - From the preview window, click on the hierarchy looking button on the column header for the lookup relationship. This will pop out a window where you can select the relationship fields in question, such as Market__r.Name
Here's a screenshot for reference and the Dax it generates for reference:
Dax from Advanced Editor:
let
Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties = true]),
Account1 = Source{[Name="Account"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Account1,{"Name", "RecordTypeId", "Market__r"}),
#"Expanded Market__r1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Market__r", {"Name"}, {"Market__r.Name"}),
#"Expanded Market__r" = Table.ExpandRecordColumn(#"Expanded Market__r1", "Market__r", {"Id", "Name", "Parent_Market__c", "Geocode__c", "Geocode_Address__c", "Parent_Market__r"}, {"Market__r.Id", "Market__r.Name", "Market__r.Parent_Market__c", "Market__r.Geocode__c", "Market__r.Geocode_Address__c", "Market__r.Parent_Market__r"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Market__r", each ([RecordTypeId] = "012130000018FFPAA2"))
in
#"Filtered Rows"
- Anonymous9 years agoNot applicable
Hi ivanoharris,
Glad to hear that the issue is resolved. You can accept your reply as solution, which will help other community members who encounter the same issue.
Thanks,
Lydia Zhang - ashik7 years agoRegular Visitor
Thanks for your suggestion. I followed the steps you have mentioned yet I am having trouble while expanding the SF Objects.
DataSource.Error: invalid query locator Details: List- hello7895677 years agoAdvocate I
Did you find a resolution to your error?
- Anonymous7 years agoNot applicable
Hi!
I used the Relationship Columns. It works but seems to seriously affect the performance to load data. It comes to x number of rows and then it takes forever to complete, and sometimes it will not.
Any hints on what I could be doing wrong?