Forum Discussion
Matching and filtering in Power Query (cyclic reference error)
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.
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 SourceHow 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.
1 Reply
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 SourceHow 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.