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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Richard_Halsall
Helper III
Helper III

Salesforce Object requiring an ID

Hi,

Please can somebody help.

I am attempting to connect to a specific Salesforce object named ContentDocumentLink

 

However this requires single or multiple id's to be passed so I get the error:

 

Richard_Halsall_0-1649671699717.png

 

I can create the list of ID's but don't know how to get the mquery to look at them and pass to the Salesforce object

 

Any help would be appreciated. Thanks

 

1 ACCEPTED SOLUTION
sfraser
New Member

I was able to create a query that returns all the ContentDocumentLink records for a given custom object.

 

To do this, I did the following:

  1. Create a query using a parameter for LinkedEntityId
  2. Create a function from the query in step 1
  3. Create a second query that uses the function in step 2

Query for step 1 is:

let
Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
in
FilterRows

 Right click on this query and select "Create Function".  You should get the following function created:

let
Source = (#"Linked Entity Id" as text) => let
Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
in
FilterRows
in
Source

 Create another query that uses the function to return the ContentDocumentLink records for a given custom object.  The custom object in this example is named OC_Review__c:

 Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48, CreateNavigationProperties=true]),
OC_Review__c = Source{[Name="OC_Review__c"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(OC_Review__c,{"Id"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Id", "OCR Id"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Doc Links", each #"Doc Links For Entity"([OCR Id])),
#"Expanded Doc Links" = Table.ExpandTableColumn(#"Invoked Custom Function", "Doc Links", {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}, {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Doc Links", each ([Id] <> null))
in
#"Filtered Rows"

View solution in original post

9 REPLIES 9
sfraser
New Member

I was able to create a query that returns all the ContentDocumentLink records for a given custom object.

 

To do this, I did the following:

  1. Create a query using a parameter for LinkedEntityId
  2. Create a function from the query in step 1
  3. Create a second query that uses the function in step 2

Query for step 1 is:

let
Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
in
FilterRows

 Right click on this query and select "Create Function".  You should get the following function created:

let
Source = (#"Linked Entity Id" as text) => let
Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data],
FilterRows = Table.SelectRows(ContentDocumentLink, each ([LinkedEntityId] = #"Linked Entity Id"))
in
FilterRows
in
Source

 Create another query that uses the function to return the ContentDocumentLink records for a given custom object.  The custom object in this example is named OC_Review__c:

 Source = Salesforce.Data("https://xxx.sandbox.my.salesforce.com", [ApiVersion=48, CreateNavigationProperties=true]),
OC_Review__c = Source{[Name="OC_Review__c"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(OC_Review__c,{"Id"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Id", "OCR Id"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Doc Links", each #"Doc Links For Entity"([OCR Id])),
#"Expanded Doc Links" = Table.ExpandTableColumn(#"Invoked Custom Function", "Doc Links", {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}, {"Id", "LinkedEntityId", "ContentDocumentId", "IsDeleted", "SystemModstamp", "ShareType", "Visibility"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Doc Links", each ([Id] <> null))
in
#"Filtered Rows"

@sfraser Many thanks for your assistance

DonLloydLopez
New Member

Hi, have you managed to find a solution to this? I'm having the same issue

IfYouBuildIt
Frequent Visitor

Is anyone able to provide a more detailed answer to this? I am having the same problem but don't know M that well. I need to bring a table into Power BI that let's me associate an SFDC SNOTE to it's parent object.

 

Thank you!

amitchandak
Super User
Super User

@Richard_Halsall , try to append this after in

"(" & Text.Combine([List Parameter], ",") & ")"

 

or

 

"('" & Text.Combine([List Parameter], "','") & "')"

Hi thanks, so I changed the syntax as suggested to this

 

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data]
in
"('" & Text.Combine([SFOpportunityID], "','") & "')"

 

and am now getting this error

 

Richard_Halsall_0-1649673263825.png

 

Hi, @Richard_Halsall 

 

You need to add the parameter to the correct position and not the end.  & means to combine strings.

 

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-janeyg-msft 
Can you explain what you mean by this.
Instead of:
let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48]),
ContentDocumentLink = Source{[Name="ContentDocumentLink"]}[Data]
in
"('" & Text.Combine([SFOpportunityID], "','") & "')"

what should the Advanced Editor look like?

Have you solved this as yet? I'm having a similar issue

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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