Forum Discussion
Lookup data from another table when text exists
Afternoon,
I'm trying to work out how I can look up a value from Table A if the text in a column of Table B contains text in a different column in Table A.
To give an example of the data:
Table A contains to following:
| TextToSearch | Result |
| Studio | STUDIO |
| En-Suite | ENSUITE |
Table B Contains
| RoomType |
| 10 Bed Cluster En-suite |
| 10 Bed En-suite |
| 10 Bed Standard En-suite |
| 5 Bed Apartment Larger Room |
| 5 Bed Bronze Room |
| 5 Bed Classic En-suite |
| 5 Bed Club Studio |
| 5 Bed Cluster |
| 5 Bed Cluster En-suite |
I would like to insert into Table B a new column that has the result of the lookup from the Text to Search in Table A into Table B.
I found a similar question here: https://community.powerbi.com/t5/Desktop/Lookup-value-from-another-table-based-on-keyword-being-in/m-p/352705#M158659
I used the CrossJoin query, which get me a new table but then I can't link this back to the orignial table as it gives me a circular linking error.
Any help will be greatly appreciated.
Thanks!
Try removing the each between "NormalisedRoomType" and (rowB) in the Advanced Editor.
12 Replies
Try this as a new custom column for TableB in the query editor:
(rowB) => List.Max( Table.SelectRows( TableA, each Text.Contains( Text.Lower(rowB[RoomType]), Text.Lower([TextToSearch]) ) )[Result] )A full sample query would look like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQcEpNUXDOKS0uSS1ScM3TLS7NLElVitWBy2ETCy5JzEtJLEKTNAXLORYkFpXkpuaVKPgkFqUDDQ3Kz89Fkncqys+rSkUXdc5JLC7OTMZmINBxSUAbS1My81FFQU7GFEEyIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RoomType = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (rowB) => List.Max( Table.SelectRows( TableA, each Text.Contains( Text.Lower(rowB[RoomType]), Text.Lower([TextToSearch]) ) )[Result] ), type text ) in #"Added Custom"AlexisOlson Thanks for this, then I'm putting this into live though, I'm not getting the results I'd expect.
Below is a screen shot from the report, a you can see the table on the left is what would be Table B and on the right is what would be table A.
Obvoiously my tables are called different in live, but I believe I updated the code correctly, here is whatI actually used:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQcEpNUXDOKS0uSS1ScM3TLS7NLElVitWBy2ETCy5JzEtJLEKTNAXLORYkFpXkpuaVKPgkFqUDDQ3Kz89Fkncqys+rSkUXdc5JLC7OTMZmINBxSUAbS1My81FFQU7GFEEyIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [room_type = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (rowB) => List.Max( Table.SelectRows( RoomTypeReview, each Text.Contains( Text.Lower(rowB[room_type]), Text.Lower([SearchText]) ) )[RoomType] ), type text ) in #"Added Custom"shown here to make sure i'm doing it in the right place:
You definitely don't want to put the full query in the Custom Column box. The first code I gave is what goes into that box. The code generated for that particular step (after you click OK on the dialog box) should look like this (abbreviated):
#"Added Custom" = Table.AddColumn(Source, "Custom", (rowB) => List.Max([...etc...]), type text)Make sure there isn't an extra "each" between "Custom," and "(rowB) =>".
That's a neat function but I don't quite follow how it works here since PhilMeach wants to add a column to TableB, not TableA.
Thank you for heloping with this. However, I'm getting an error saying
"Expression.Error: A cyclic reference was encountered during evaluation."
I'm struggling to work out in your answer where the TextToSearch looks up in the RoomType column?
This now works Perfectly!! Which solution should I mark as accepted?
You can mark any or all solutions that resolved your problem. For example, message 2 or 10 or both.
