Salesforce Object requiring an ID

[Richard_Halsall]

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:

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

[sfraser · Accepted solution]

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"

[Richard_Halsall]

@sfraser Many thanks for your assistance

[DonLloydLopez]

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

[IfYouBuildIt]

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]

@Richard_Halsall , try to append this after in

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

or

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

[Richard_Halsall]

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

[v-janeyg-msft]

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.

[ruppj]

@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?

[DonLloydLopez]

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