Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |