Forum Discussion

Richard_Halsall's avatar
4 years ago
Solved

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:

    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"

10 Replies

  • 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 , try to append this after in

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

     

    or

     

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

    • Richard_Halsall's avatar
      Richard_Halsall
      Helper 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-msft's avatar
        v-janeyg-msft
        Community 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.

  • Anonymous's avatar
    Anonymous
    Not 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!

  • 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.