POWERPIVOT lookup based on 2 columns/conditions MANY TO MANY relationship

[dluhut]

Hello,

I hope the tables below can give you an idea of what I'd like to accomplish.

Basically, given 2 tables, the transaction table and lookup table, In POWERPIVOT, I'd like to create a column where in the 'Transaction Table', I can do a function where I can do a lookup and return the value from another table.

So how can I go about doing it?

PS: I'm using EXCEL 2013

Transaction Table

GL	Functional Area
1	Z000
1	Z001
1	Z002
1	Z003
1	Z003
1	Z004
2	Z000
2	Z001
2	Z002
2	Z003
2	Z003
2	Z004

<tbody>
</tbody>

Lookup Table

Group	GL	Functional Area
Grocery Expense	1	Z000, Z001
Restaurant Expense	1	Z002
Entertainment Expense	1	Z003
Other Expense	1	Z004
Mortgage Expense	2	Z000, Z001
Utilities Expense	2	Z002
Interest Expense	2	Z003
Amortization/Depreciation	2	Z004

<tbody>
</tbody>

Desired Result in Transaction Table

GL	Functional Area	Group
1	Z000	Grocery Expense
1	Z001	Grocery Expense
1	Z002	Restaurant Expense
1	Z003	Entertainment Expense
1	Z003	Entertainment Expense
1	Z004	Other Expense
2	Z000	Mortgage Expense
2	Z001	Mortgage Expense
2	Z002	Utilities Expense
2	Z003	Interest Expense
2	Z003	Interest Expense
2	Z004	Amortization/Depreciation

<tbody>
</tbody>

Crosspost: http://www.excelguru.ca/forums/showt...y-relationship and https://www.excelforum.com/excel-fo...vot-lookup-based-on-2-columns-conditions.html but still no help/answer given.

 

[gazpage]

PowerPivot for Excel doesn't support many to many relationships (PowerBI desktop and SSAS do), or multi column relationships.

That said I think you want multi column relationships, but not many to many. If it was many to many then you would have multiple items for each row by definition.

The best way to deal with multi column relationships is at the data import, using Power Query. Import both sets of data and then merge queries. Select both the GL and function columns in both tables and select your join method (you will want the left outer I think). Click ok and jobs done, should take < 5mins.

 

[Chihiro]

Solution provided at below.
https://www.excelforum.com/excel-fo...ased-on-2-columns-conditions.html#post4666878

 

[TicaTorch1]

Since I upgraded to 2016 Office, my tables properties are grayed out in Query Editor. I need it to go back to Table View. Can another assist?

 

[Matt Allington]

It's a bug. You will need to reload until t is fixed

 

[TicaTorch1]

do you mean uninstall Powerpivot and re-install?

 

[Matt Allington]

no sorry. This is a bug that can't be fixed until a fix is released by MS. In the meantime, you will need to remove the table and reload it to Power Pivot.