Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello Power BI Community,
I have two tables in Power Query Editor pulling from 2 separate SharePoint Lists - a 'master project' table containing project names, and a 'shortlist of items assigned to projects; table containing each task assigned to a project and their approval status. Both tables are linked using the Project Title column as a parent/child relationship and appear as such below:
Master Project Table
Project Title |
Project A |
Project B |
Project C |
Shortlist of items assigned to Projects
Project Title | Item | Item Approval Status |
Project A | Item 1.1 | Approved |
Project A | Item 1.2 | Approved |
Project A | Item 1.3 | Declined |
Project B | Item 2.1 | Approved |
Project B | Item 2.2 | Declined |
Project B | Item 2.3 | Approved |
Project C | Item 3.1 | Approved |
Project C | Item 3.2 | null |
Project C | Item 3.3 | null |
What I am looking to achieve is I want to create another column in the Master Project list table that first FILTERS the project title in the 'Master Project' table to the corresponding project title in the 'Shortlist of items assigned to Projects' Table, then checks whether ALL approval statuses for that project have a value assigned to it that is NOT blank (null), then populates the new column "Overall Project Status" with the status update (i.e, "All Approvals Confirmed", if the Item approval status doesn't contain a null value for Project Title being looked up, and "Awaiting Approval" if otherwise (there is a null value for one of the Item Approval Statuses being looked up)). Essentially, the new resulting column in the master table should look something like this:
Master Project Table (with new column)
Project Title | Overall Project Status |
Project A | All Approvals Confirmed |
Project B | All Approvals Confirmed |
Project C | Awaiting Approval |
* The Overall Project Status for Project C is 'Awaiting Approval' since there are blank values for some items for that project in the Shortlist of items assigned to Projects.
How would I formulate this logic into a new column to produce the above result?
Thanks in advance, your help is greatly appreciated!
Jackson
Solved! Go to Solution.
Hi @Anonymous ,
How about this:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRUvAsSc1VMNQzBLEdCwqK8stSU5RidbCrMiJKlTGI7ZKanJOZh6bKCa7KCI+NyKqMiDLLGKdZznBVxnhsRFYFshGnpDFYMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #" Item" = _t, #" Item Approval Status" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {" Item", type text}, {" Item Approval Status", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{" Item Approval Status"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([#" Item Approval Status"] = null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Project Title"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Project Title"}, #"Removed Duplicates", {"Project Title"}, "Removed Duplicates", JoinKind.LeftOuter), #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Project Title"}, {"Removed Duplicates.Project Title"}), #"Added Custom" = Table.AddColumn(#"Expanded Removed Duplicates", "Custom", each if [Removed Duplicates.Project Title] is null then "All Approvals Confirmed" else "Awaiting Approval"), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Project Title", "Custom"}), #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1") in #"Removed Duplicates1"
There are quite a few steps involved and I am sure one can do it in less steps and hence achieve better performance. But the code above might still help!
Let me know if this works for you 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Anonymous ,
How about this:
Before:
After:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRUvAsSc1VMNQzBLEdCwqK8stSU5RidbCrMiJKlTGI7ZKanJOZh6bKCa7KCI+NyKqMiDLLGKdZznBVxnhsRFYFshGnpDFYMhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Title" = _t, #" Item" = _t, #" Item Approval Status" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Title", type text}, {" Item", type text}, {" Item Approval Status", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{" Item Approval Status"}), #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([#" Item Approval Status"] = null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Project Title"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Project Title"}, #"Removed Duplicates", {"Project Title"}, "Removed Duplicates", JoinKind.LeftOuter), #"Expanded Removed Duplicates" = Table.ExpandTableColumn(#"Merged Queries", "Removed Duplicates", {"Project Title"}, {"Removed Duplicates.Project Title"}), #"Added Custom" = Table.AddColumn(#"Expanded Removed Duplicates", "Custom", each if [Removed Duplicates.Project Title] is null then "All Approvals Confirmed" else "Awaiting Approval"), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Project Title", "Custom"}), #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1") in #"Removed Duplicates1"
There are quite a few steps involved and I am sure one can do it in less steps and hence achieve better performance. But the code above might still help!
Let me know if this works for you 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
68 | |
25 | |
18 | |
12 |