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
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
Helper IIIHello 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!!
- ImkeF
Hi 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/ )