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

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