Working with 2 Lookup tables in Power Query

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/