Working with 2 Lookup tables in Power Query

[Anonymous]

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 

[tackytechtom · Accepted 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/