Forum Discussion
Searching a column in table A using data from a column in table B
Hi Anonymous
"Custom1" is not created with adding a custom column. It is created in the formula bar directly just like below. You can right click #"Renamed Columns2" step, select "Insert Step After" then modify the code in the formula bar.
For the second step, it is created with adding a custom column. But it is not based on the "Custom1" step. Instead, it is based on the previous step of "Custom1". You need to modify the step name.
You can also edit the code in Advanced editor directly. It looks like this.
Best Regards,
Jing
Ive been trying to get this to work but running into an expression error.
= Table.AddColumn(#"Renamed Columns2", "SFID", each let vsubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0})))))
Expression.Error: The name 'vSubject' wasn't recognized. Make sure it's spelled correctly.
Anonymous In power query, it is case-sensitive. You need to keep these two places Case consistent. Otherwise it will be treated as two names which caused this error.
In addition, the custom list is to extract the Name and ID columns from Table B to be used in Table A. It won't affect any visuals if you directly use Table B columns in them. Do you have other columns in Table B that also need to be brought into Table A?
Best Regards,
Jing
Getting close as I was able to make some progress fixing the capitilization. The new column is getting created as a table and it does not let me expand the table to see the value.
On the visual it is appearing like this. SFID in your example is Table B "Custom".
= Table.AddColumn(#"Renamed Columns2", "Custom", each List.Zip({#"PHM Assignment List"[Payee FB Number], #"PHM Assignment List"[Account ID]}))
Followed by:
= Table.AddColumn(Custom1, "SFID", each Table.AddColumn(#"Renamed Columns2","Account ID", each let vSubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0}))))))
Hi Anonymous
"Custom1" is not created with adding a custom column. It is created in the formula bar directly just like below. You can right click #"Renamed Columns2" step, select "Insert Step After" then modify the code in the formula bar.
For the second step, it is created with adding a custom column. But it is not based on the "Custom1" step. Instead, it is based on the previous step of "Custom1". You need to modify the step name.
You can also edit the code in Advanced editor directly. It looks like this.
Best Regards,
JingThat absolutely worked! Thank you for the help!!!!