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 contain...
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

     

     

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.