Forum Discussion
Lookup in Power Query Editor
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?
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 🙂
1 Reply
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 🙂