Forum Discussion
Searching a column in table A using data from a column in table B
I have a need to search a string in Column 1 from Table A with column 1 Table B so that if a match exists, it displays the value of Column 2 in Table B. Table A = Outlook 365 Data (Subject [Text]) ...
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
dufoq3
Community Champion
Community ChampionHi Anonymous, what about this?
Result (Table1 with matching ID's from Table2)
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjRSitWJVnJz9zAxBbMcHR3NzJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vJ3VvBNrMxXcM7JzMtMVlB2dHI2NFIILE0sKkktyqlU8E1NLcnMS1fSUTI0MlaK1YFocSnNza2E63F2cTUyVohMTUTVYGJqBtfglZ+Rp+CSn6qg7ObuYWKq4JufV5KBotrcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Meeting" = _t, ID = _t]),
Table2CalendarMeetings = List.Buffer(Table2[Calendar Meeting]),
Ad_Table2ID = Table.AddColumn(Table1, "Table2 ID", each
[ a = List.PositionOf(Table2CalendarMeetings, [Column1], Occurrence.First, (x,y)=> Text.Contains(x, y, Comparer.OrdinalIgnoreCase)), //First match position
b = if a = -1 then null else Table2[ID]{a}
][b] )
in
Ad_Table2ID