Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
2002181226 | Lower | Outstanding |
2002181226 | Lower | Outstanding |
2002181226 | Lower | Outstanding |
2002181226 | Upper | Done |
2002181226 | Upper | Outstanding |
2002181226 | Upper | Outstanding |
2002181227 | Lower | Outstanding |
2002181227 | Lower | Outstanding |
2002181227 | Lower | Outstanding |
2002181228 | Lower | Outstanding |
2002181228 | Lower | Outstanding |
2002181228 | Lower | Outstanding |
2002181229 | Lower | Done |
2002181229 | Lower | Done |
2002181229 | Lower | Done |
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
2002181226 | Outstanding |
2002181227 | Outstanding |
2002181228 | Outstanding |
2002181229 | Done |
I would really appreciate some help as i have been stuck on this for half a day.
Solved! Go to Solution.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!!
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-po... )
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello @ImkeF
Yes the second table does contain all the values from the first table. Thank you very much!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
27 | |
14 | |
14 | |
12 |