Lookupvalue in Query Editor from other table

[nj-matt]

I have 2 tables that are transformed before being combined in a 3rd table.

In table 1, I have identifed unique IDs and created a column for each with the result set to "YES".

Table 2 will always contain more values than table 1 - so essentially I want to identify these YES's from table 1 before removing and combining in table 3.

Example -->

Table1

Unique_ID	YES?
A-12345	YES
A-12346	YES

Table2

Unique_ID	Table 1 YES?
A-12343	NO
A-12345	YES
A-12346	YES
A-12347	NO

From there I would filter out the YES from table 2 before then combining both tables in the next step.

How do I do this in power query in Table2?

[parry2k]

@nj-matt not sure what you mean. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[parry2k]

@nj-matt in table2 add a step to filter "Yes" and then append it in table1.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[nj-matt]

I would prefer to do this before the merge to avoid duplicate values that will cause issues on subsequent relationships.

[v-kelly-msft · Accepted solution]

Hi  @nj-matt ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1NDI2MVbSUfLzV4rVgQmYAgUiXYORRMwwRMyhmmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unique_ID = _t, #"Table 1 YES?" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique_ID", type text}, {"Table 1 YES?", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each (let 
currentID = [Unique_ID]
in 
Table.SelectRows(#"Table 1", each [Unique_ID] = currentID))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"YES?"}, {"Custom.YES?"})
in
    #"Expanded Custom"

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!