Forum Discussion

PhilMeach's avatar
PhilMeach
Regular Visitor
4 years ago
Solved

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:   ...
Need Help
Tips and Tricks
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

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

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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's avatar
PhilMeach
Regular Visitor
4 years ago

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's avatar
    AlexisOlson
    Super User
    4 years ago

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