Forum Discussion

Bjorn_C's avatar
Bjorn_C
Frequent Visitor
4 years ago
Solved

Check if a value occues in a varialbe list

Hello,    can someone help me out with a piece of M code that allows me to make a check on the data quality of an input file.    I have 2 tables:  tbl_data :  this table if the result of an inpu...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Bjorn_C 

     

    I agree with HotChilli , you need to select both postcode and city columns as matching columns, just like below. Hold on Ctrl key on keyboard and select columns in the same sequence. 

     

    The result is like this 

     

    Then add a custom column to get the Check result. 

    if Table.IsEmpty([Tbl_Bpost]) then "NOK" else "OK"

     

    All M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAxMFDSUfLPLy5JzUtJVYrVQYiVpqTmJZUWpSMLRiXmpZTl5xdBlVoag0X9MlNLy1OLskEoD1nCMTGnuASvAJiKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"postal code" = _t, city = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"postal code", type text}, {"city", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"postal code", "city"}, Tbl_Bpost, {"Postcode", "Plaatsnaam"}, "Tbl_Bpost", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Check", each if Table.IsEmpty([Tbl_Bpost]) then "NOK" else "OK")
in
    #"Added Custom"

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
4 years ago

You can do a Merge (comparing on 2 pairs of columns). It would be a Left Outer.  This would return a column containing a table of matched rows.

Then you can add a column which uses Table.IsEmpty to test this returned column and return true or false

---

Alternatively, add a column using Table.ContainsAny    -> the documentation on this is not great but if you have a go, I will help if you get stuck

  • Bjorn_C's avatar
    Bjorn_C
    Frequent Visitor
    4 years ago

    I loaded both tables into PowerQuery made a merge of both. 

    For the example of postal code "8400" in my tbl_data, I have again the 3 rows, and due to to the merge I have a table matching each row  (I did not expand the tables). 

     

    It is the final step (comparing for each line if the value in the column city, matches at least one of the value in the 

     

     

    the code so far is (including the filter on postam code "8400") : 

    let
    Source = Table.NestedJoin(Tbl_data, {"postal code"}, Tbl_Bpost, {"Postcode"}, "Tbl_Bpost", JoinKind.LeftOuter),
    #"Filtered Rows" = Table.SelectRows(Source, each [postal code] = "8400")
    in
    #"Filtered Rows"

     

    Can someone help me with the next line of code to add the custom column? 

     

    Regards

    Bjorn 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Your next step would be:

       

      = Table.AddColumn(#"Filtered Rows", "Valid", each if List.Contains([Tbl_Bpost][Postcode], [city]) then "OK" else "NOK", type text)

       

      --Nate

      • Bjorn_C's avatar
        Bjorn_C
        Frequent Visitor
        4 years ago

        Hi Nate,

        thanks for the input. However I do not get the expected result. 

        In the first and third row I expected "OK"  

         

         

        Below the code I am using 

         

        let
        Source = Table.NestedJoin(Tbl_data, {"postal code"}, Tbl_Bpost, {"Postcode"}, "Tbl_Bpost", JoinKind.LeftOuter),
        #"Filtered Rows" = Table.SelectRows(Source, each [postal code] = "8400"),
        Custom1 = Table.AddColumn(#"Filtered Rows", "Valid", each if List.Contains([Tbl_Bpost][Postcode], [city]) then "OK" else "NOK", type text)
        in
        Custom1

         

        Any idea where it goes wrong 

        thx

         

        Bjorn