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
Hi Anonymous
You may try my solution as below. I also attached a demo pbix at bottom. Hope this would be helpful.
Table A:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vJ3VvBNrMxXcM7JzMtMVlB2dHI2NFIILE0sKkktyqlU8E1NLcnMS1eK1cGh2ASb4lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Subject", type text}}),
Custom1 = List.Zip({#"Table B"[Name],#"Table B"[ID]}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let vSubject = [Subject] in List.Last(List.First(List.Select(Custom1, each Text.Contains(vSubject, _{0})))))
in
#"Added Custom"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
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.