Lookup in Power Query Editor

[rpinxt]

I have 2 table which I cannot merge due to granularity:

This is the main:

And this is the other:

So wanted to merge them on country and then get category in the main table.

Looked liked it worked but then I noticed my lines got doubeld.

Most likely because the main table has Business and therefore the country is in for every business.

I could add business to the lookup table but then I would always be doing double maintenance if new countries were in scope.

Was thinking of not merging but just do a lookup from the main table into the lookup tabel and then get category from the lookup table in my custom column in the main table.

What would the M-code be for something (simple) like this?

[rpinxt · Accepted solution]

Ok I was able to find a solution found in a power query video explaining this.
Would think it would be easier, not that it is hard but it looks more like a workaround.

= #"*V* Category"[Category]{List.PositionOf(#"*V* Category"[Country],[Country])}

Looking first up where the poistion is of the category

x = List.PositionOf(#"*V* Category"[Country],[Country])

and than use that to get the actual category...
- #"*V* Category"[Category]{ x }

Maybe there are better and easier solutions for when merge doesn't work but I will go with this for now 🙂