cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

salesforce object connector - Lookup relationship support ?

Does anyone know if lookup relationships are supported in the Salesforce Object Connector.  For example, I'd like to query the object Account and pull in Name from a lookup object called Market.  Typically within a normal SOQL query I would query as such:

 

SELECT Market__r.Name FROM Account

 

When I try and use the Advanced Editor to modify the Dax query to include this it errors out.  

 

Thank you for the help.

Ivano

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: salesforce object connector - Lookup relationship support ?

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:

 

Lookup-Relationship.jpg

 

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"

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: salesforce object connector - Lookup relationship support ?

Hi @ivanoharris,

What error message do you get when modifying the code in Advanced Editor?  Please follow the instructions in this similar blog to  define lookup relationships in Advanced Editor.

However, comparing to modifying code in Advanced Editor, it is easier to use the Merge Queries feature in Power BI Desktop. Merge lets you join two data queries and retrieve lookup object , for more details, please review this similar blog: https://businessintelligist.com/2015/09/15/power-bi-desktop-merge-query-options/ .

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: salesforce object connector - Lookup relationship support ?

Lydia,

It's a lot more work to use merge queries on the PowerBI side than it is to leverage the inherent built-in SOQL query support for querying lookup relationships.

 

If the connector supports this, I'd love to know.  If not, I'd think you'll want to look into it. 

 

Ivano

Microsoft
Microsoft

Re: salesforce object connector - Lookup relationship support ?

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: salesforce object connector - Lookup relationship support ?

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:

 

Lookup-Relationship.jpg

 

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"

View solution in original post

Highlighted
Microsoft
Microsoft

Re: salesforce object connector - Lookup relationship support ?

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

Re: salesforce object connector - Lookup relationship support ?

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
Highlighted
Helper I
Helper I

Re: salesforce object connector - Lookup relationship support ?

Did you find a resolution to your error?

Highlighted
Frequent Visitor

Re: salesforce object connector - Lookup relationship support ?

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?

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors