Matching and filtering in Power Query (cyclic reference error)

user180618 — Mon, 15 May 2023 11:55:06 GMT

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.

Re: Matching and filtering in Power Query (cyclic reference error)

lbendlin — Wed, 17 May 2023 00:04:15 GMT

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.

topic Re: Matching and filtering in Power Query (cyclic reference error) in DAX Commands and Tips

Matching and filtering in Power Query (cyclic reference error)

Re: Matching and filtering in Power Query (cyclic reference error)