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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.