Power Query AddColumn with data from other table runs slow with List.PositionOf

[elkay73]

I need to capture the data from the 'Answer' Column in Table1 into the new 'Answer' column in Table2.  Where the tables have a many to many relationship in the Column 'Names'.  And if there is no match for 'Names' give me the text "No".  My current code is :   

let
Source = Table2,
#"Added Custom" = Table.AddColumn(Source, "Answer", each Table1[Answer]{List.PositionOf(Table1[Names],[Names])}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Answer", "No"}})
in
#"Replaced Errors"

This works but takes a couple minutes to run.  Suggestions for a faster way?  Maybe Table.Buffer but I can't figure out the syntax to use with my current code.

(My tables have under 1000 records in them so it shouldn't be taking that long.)

[elkay73 · Accepted solution]

I ended up adding a column with "= Table.ReplaceValue(#"Replaced Errors",null,"No",Replacer.ReplaceValue,{"SelfMed"})" as the code.

[v-csrikanth]

Hi @elkay73 
As highlighted by @lbendlin , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolve
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[lbendlin]

Instead of replacing errors use try ... otherwise ... in the original query.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[d_m_LNK]

It's not power query but you could create a DAX calculated column on the Table2 table for this:

Answer = 
IF( ISBLANK(SelectedValue('Table1'[Answer])), "No", SelectedValue('Table1[Answer])