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.

     

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 

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      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.

       

      • Bjorn_C's avatar
        Bjorn_C
        Frequent Visitor
        4 years ago

        Thanks everyone!!!

        it is working now 🙂 

         

        Regards

        Bjorn