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"
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!
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?