Forum Discussion
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:
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
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:
- Create a query using a parameter for LinkedEntityId
- Create a function from the query in step 1
- 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
FilterRowsRight 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
SourceCreate 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"
10 Replies
- sfraserNew 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:
- Create a query using a parameter for LinkedEntityId
- Create a function from the query in step 1
- 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
FilterRowsRight 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
SourceCreate 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_HalsallHelper IV
sfraser Many thanks for your assistance
- amitchandakSuper User
Richard_Halsall , try to append this after in
"(" & Text.Combine([List Parameter], ",") & ")"
or
"('" & Text.Combine([List Parameter], "','") & "')"
- Richard_HalsallHelper IV
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-msftCommunity Support
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.
- AnonymousNot applicable
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!
- DonLloydLopezNew Member
Hi, have you managed to find a solution to this? I'm having the same issue
- metricaPost Prodigy
Hi everyone,
For anyone landing here from search and not wanting to mess with custom M functions - there's a much simpler alternative worth a look.
Metrica Power BI Connector for Salesforce on AppExchange lets you build the filter directly in a UI on the Salesforce side - pick the object, save, and the dataset shows up in Power BI ready to go. No M code, no function chain, no Power Query gymnastics. Works the same way for any other object that needs a required filter.
30-day free trial, no card. Docs at https://metricasoftware.com/, happy to do a quick demo if useful - just reach out.
Cheers,
Metrica Team.