Forum Discussion

Adham's avatar
Adham
Icon for Helper III rankHelper III
6 years ago
Solved

If a certain value exists in another table for a column then display value

Hello All,

 

I really do need help with the following. I have got a table with unique ids.

 

sample_id

2002181226
2002181227
2002181228
2002181229

 

 

And another table containing the following columns.

 

sample_id       phase    Custom

2002181226LowerOutstanding
2002181226LowerOutstanding
2002181226LowerOutstanding
2002181226UpperDone
2002181226UpperOutstanding
2002181226UpperOutstanding
2002181227LowerOutstanding
2002181227LowerOutstanding
2002181227LowerOutstanding
2002181228LowerOutstanding
2002181228LowerOutstanding
2002181228LowerOutstanding
2002181229LowerDone
2002181229LowerDone
2002181229LowerDone

 

What i want to do is enter a new column in the first table which does the following:

 

If any row in custom column contains the value 'Outstanding' for Table_2[sample_id] = Table_1[sample_id] then display Outstanding, else display Done. The result should look like this:

 

sample_id       Result

2002181226Outstanding
2002181227Outstanding
2002181228Outstanding
2002181229Done

 

I would really appreciate some help as i have been stuck on this for half a day.

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Adham 

    this can be done with a special merge of both tables like so:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMDK0MDQyMlOK1UHimqNyLVC5lkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sample_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sample_id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"sample_id"}, Table.Distinct(Table.Sort(Table_2, {{"Custom", Order.Descending}}), {"sample_d"}), {"sample_d"}, "Table_2", JoinKind.LeftOuter),
    #"Expanded Table_2" = Table.ExpandTableColumn(#"Merged Queries", "Table_2", {"Custom"}, {"Custom"})
in
    #"Expanded Table_2"

     

     

    So you merge the Table_2 to Table_1 on "sample_id" and then tweak the code so that only one row from the Table_2 remains: The one that is the first after that table has been sorted on column "Custom" in descending order.

     

    ... attaching link to file

     

     

5 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Adham 

    this can be done with a special merge of both tables like so:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMDK0MDQyMlOK1UHimqNyLVC5lkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sample_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sample_id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"sample_id"}, Table.Distinct(Table.Sort(Table_2, {{"Custom", Order.Descending}}), {"sample_d"}), {"sample_d"}, "Table_2", JoinKind.LeftOuter),
    #"Expanded Table_2" = Table.ExpandTableColumn(#"Merged Queries", "Table_2", {"Custom"}, {"Custom"})
in
    #"Expanded Table_2"

     

     

    So you merge the Table_2 to Table_1 on "sample_id" and then tweak the code so that only one row from the Table_2 remains: The one that is the first after that table has been sorted on column "Custom" in descending order.

     

    ... attaching link to file

     

     

    • Adham's avatar
      Adham
      Icon for Helper III rankHelper III
      6 years ago

      Hello ImkeF,

       

      Thank you very much for the solution! I really do appreciate your detailed explanation and ofcourse the file attached! The thing is with this data that more and more sample ids are going to be added. So i am thinking wouldn't it be best to:

      - start off with table 2, which is from the database

      - reference it to another table so that it is automatically updated as the database is updated

      - Sort the Custom column in descending order

      - Remove Duplicates based on sample id

      Then i get my answer. Please do let me know what do you think!

       

      Thank you again!!

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Adham , 

    You also could refer to below M code to see whether it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMDK0MDQyMlOK1UHimqNyLVC5lkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sample_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sample_id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"sample_id"}, Table.Sort(#"Table (5)", {{"Custom", Order.Descending}}), {"sample_id"}, "Table (5)", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [#"Table (5)"][Custom]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table (5)"})
in
    #"Removed Columns"

    Table (5) is second table.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.