Returning max value of table if row is in list of another table

[Anonymous]

Hey guys I have two tables, one Dimension table and one fact table

Fact

ID	ListOfStrings	MaxIndex
1	{ mouse, elephant }	3

Dim

Index	Description
1	mouse
2	tiger
3	elephant

My goal is to to write an expression which is able to check the dimensiontable for all Strings in ListStrings and return the maxIndex

I already had alot of different expression, my current one is:

= Table.AddColumn(Source, "MaxIndex", each

List.Max(

List.Select(

Table.SelectRows(#"Dim",

each List.Contains([ListOfStrings],[Description]))[Index],

each _ <> null))

)

but I get this error:Expression.Error: The field 'ID' of the record wasn't found.

It seems like an easy problem but I sadly cant figure it out. Any help is appreciated.

Edit:

It seems like I cant pass the column directly or any other parameter of the table I'm currently working in. Is there a way to declare a variable before I use the first List. method? Or can I access the first "each" somehow?

Edit2:

Made it work by extracting the logic into a function 

mFunction =
(list) => List.Max(

List.Select(

Table.SelectRows(#"Dim",

each List.Contains(list,[Description]))[Index],

each _ <> null))

= Table.AddColumn(Source, "MaxIndex", each myFunction([listOfStrings])

tyvm

[wdx223_Daniel]

=let a=Table.Buffer(Table.Sort(Dim,{"Index",1})) in Table.AddColumn(Fact,"MaxIndex",each Table.Skip(a,(x)=>not List.Contains([ListOfStrings],x[Description])){0}?[Index]?)

[Anonymous]

Thank you for our solution. Unfortunatly your solution as well as mine takes way to long to evaluate and reads to much data. I stopped on 156MB, the facttables original size is only 3MB.

My Stringlist might be the problem because its a function aswell. Well I stopped PBI from reading all the data by bufering the fact table also but it still takes way to long to evaluate. Do you have a hint why this has such a bad performance?

StringListFunction = 
(buffered_table,ID) => 
List.Distinct(
List.Select(
Table.SelectRows(buffered_table, each [ID]=ID)[Description],
each _ <> null
))