Forum Discussion
Helper IIIIf a certain value exists in another table for a column then display value
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
Community ChampionHi Adham
not sure I can follow your explanation, but if the second table contains all values from the first table, there is no need for a merge at all. Simply filter the second table like I did in the merge (or a similar way).
A little comment to dax ' solution: Although it looks pretty similar to mine, it will run much slower on large datasets, as it is not using a primary key for the merge. (an explanation can be found here: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/ )
