Forum Discussion
Lookup data from another table when text exists
Try removing the each between "NormalisedRoomType" and (rowB) in the Advanced Editor.
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) =>".
Hi AlexisOlson I'm very sorry, but I'm not understanding this. I've tried to search online to research custom columns but I'm struggling to understand what I'm doing wrong and I'm struggling to understand the rowB function bit.
What I've done, is create a custom column ad added the code as suggested, see screen show. But this just gives me a column that says [function] if I expand the function, it's asking for a value for rowB.
I've also looked at the advanced editor, and the code doesn't look like that which is suggested, so I'm obviously doing something wrong, but I'm not sure what sorry. If you can help any further that would be greatly appreciated.
Try removing the each between "NormalisedRoomType" and (rowB) in the Advanced Editor.