Lookup data from another table when text exists

[PhilMeach]

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...

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!

[PhilMeach]

This now works Perfectly!! Which solution should I mark as accepted?

[AlexisOlson]

You can mark any or all solutions that resolved your problem. For example, message 2 or 10 or both.

[Anonymous]

Are you aware that you have the word "apartment" spelled differently in the look up text?

[Anonymous]

Sorry, I misread the question. In your case, I would Split by Delimiter Table B "RoomType", using a space as the delimiter, making sure you choose Split Once, from the end.

Now you can just Left Join Table B to Table A using the new column in Table B and the TextToSearch column in Table A.

--Nate

[Anonymous]

Even easier to use

= Table.AddColumn(#"Table A", "Lookups", each Table.FindText(#Table B", [TextToSearch]), type text)

--Nate

[PhilMeach]

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?

[AlexisOlson]

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.

[AlexisOlson]

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"

[PhilMeach]

@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:

[AlexisOlson]

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) =>".

[PhilMeach]

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.

[AlexisOlson · Accepted solution]

Try removing the each between "NormalisedRoomType" and (rowB) in the Advanced Editor.