Custom Column matching table values

[HarveyM]

Hi,

I currently have two tables, table 1 and table 2. In table 1 there is an envID, which matches to table 2. In table 2 a bgID is assocaited with the environment IDs. Multiple envIDs can share the same bgID. I want to create a custom column in table 1 that can match envIDs with table 2, and then set the value to the corresponding bgID. Below is an example of the setup.

Thank you

[Greg_Deckler]

@HarveyM Couple ways, here is one:

Column = 
  VAR __envID = 'Table 1'[envID]
RETURN
  MAXX(FILTER('Table 2',[envID] = __envID),[bgID])

[HarveyM]

Hi @Greg_Deckler 

When using the method you sent over, I get a 'Token Eof expected' under the __envID variable.

It happens before and after I have changed table 1 & 2 over to the real table names.

Could you please advise. Thank you 🙂 

[Greg_Deckler · Accepted solution]

@HarveyM Dangit, sorry gave you a DAX solution when you posted in the Power Query forum. Yeah, that code will not work in Power Query, it is for a DAX calculated column. What I would recommend in Power Query would be to do a Merge query.