Forum Discussion

user180618's avatar
user180618
Icon for Helper I rankHelper I
3 years ago
Solved

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 ( VALUE...
  • lbendlin's avatar
    3 years ago

     

    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.