Power Query .SelectRows filter based on a list

Question

I created a query which I want to be able to filter based on a column from another table, how can I achieve this as it doesn't seem to work?

let
    Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties=true]),
    MyCustomObject = Source{[Name="MyCustomObject "]}[Data],
    accnt = Source{[Name="Account"]}[Data],
    accntId = Table.SelectColumns(accnt, {"Id"}),
    accntList = Table.ToList(accntId, Combiner.CombineTextByDelimiter(",")),
    #"Filtered" =  Table.SelectRows(MyCustomObject, each List.Contains(accntList, [AccountId__c]))

 in 
    #"Filtered"

I've also tried using List.Contains(accnt[Id]) but no joy

If I changed it to each (AccountId__c = "Some Value Here") it works but not with a list.

Thanks

Answer 1

Are the IDs unique in each column? If so, you can do a Merge with an Inner Join Kind, which you can do through the UI (in the Home ribbon tab) if you put MyCustomObject and accnt into separate queries.

If the IDs are not unique, you can still do a Merge, but you'll have to do one of the Outer Join Kinds, expand the new column, and then filter out the column with the null ID.

Answer 2

It seems the problem is in the line:

accntList = Table.ToList(accntId, Combiner.CombineTextByDelimiter(",")),

I use to transform the field in a list with this line:

accntList = List.Distinct(accnt[Id]),

The code will be something like:

let
    Source = Salesforce.Data("https://login.salesforce.com/", [CreateNavigationProperties=true]),
    MyCustomObject = Source{[Name="MyCustomObject "]}[Data],
    accnt = Source{[Name="Account"]}[Data],
    accntList = List.Distinct(accnt[Id]),
    #"Filtered" =  Table.SelectRows(MyCustomObject, each List.Contains(accntList, [AccountId__c]))

 in 
    #"Filtered"