Matching and filtering in Power Query (cyclic reference error)

[user180618]

I would like to do the equivalent of the below calculated column in Power BI as a Power Query expression. This is a follow-up to my previous question.

Matched =
MAXX (
    FILTER (
        VALUES ( Table2[US_english] ),
        CONTAINSSTRING ( Table1[Word], Table2[US_english] )
    ),
    Table2[US_english]
)

I have tried the following:

let x= Table.AddColumn(Table1, "Column2.matched", each Text.Combine(List.Select(Table2[US_english], (x) => Text.Contains([Column1.words], x)),",")) in x

But it gives me a Expression.Error: A cyclic reference was encountered during evaluation.

[lbendlin · Accepted solution]

Table 1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PUxJCoAwDPxKyVH6o9JDuihFtJJOEX9vctDL7EwIlNvGUoE6vFOtmCagtC5myhR5KPpAEM77mA0udaAfWv6R6nFXxsUnvrzZY7EBxfgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.words = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column2.matched", (k)=> try Table.SelectRows(#"Table 2", each List.Contains(Text.Split(k[Column1.words],", "),[US_english]))[US_english]{0} otherwise "no match")
in
    #"Added Custom"

Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs5ML1bSUUrTAlMppUVFlUqxOkDxxLyUSqBIcXlqaglIKic/JycztRgsCRRMLClIzANLlBQlJmeDZJCYsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [US_english = _t, UK_english = _t, AU_english = _t])
in
    Source

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

NOTE:  You had trailing spaces at the end of sweatpants in your sample data.