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
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]GL
[/TD]
[TD]Functional Area
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z000
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z001
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z002
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z004
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z000
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z001
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z002
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z004
[/TD]
[/TR]
</tbody>[/TABLE]



Lookup Table
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Group
[/TD]
[TD]GL
[/TD]
[TD]Functional Area
[/TD]
[/TR]
[TR]
[TD]Grocery Expense
[/TD]
[TD]1
[/TD]
[TD]Z000, Z001
[/TD]
[/TR]
[TR]
[TD]Restaurant Expense
[/TD]
[TD]1
[/TD]
[TD]Z002
[/TD]
[/TR]
[TR]
[TD]Entertainment Expense
[/TD]
[TD]1
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]Other Expense
[/TD]
[TD]1
[/TD]
[TD]Z004
[/TD]
[/TR]
[TR]
[TD]Mortgage Expense
[/TD]
[TD]2
[/TD]
[TD]Z000, Z001
[/TD]
[/TR]
[TR]
[TD]Utilities Expense
[/TD]
[TD]2
[/TD]
[TD]Z002
[/TD]
[/TR]
[TR]
[TD]Interest Expense
[/TD]
[TD]2
[/TD]
[TD]Z003
[/TD]
[/TR]
[TR]
[TD]Amortization/Depreciation
[/TD]
[TD]2
[/TD]
[TD]Z004
[/TD]
[/TR]
</tbody>[/TABLE]


Desired Result in Transaction Table
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]GL
[/TD]
[TD]Functional Area
[/TD]
[TD]Group
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z000
[/TD]
[TD]Grocery Expense
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z001
[/TD]
[TD]Grocery Expense
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z002
[/TD]
[TD]Restaurant Expense
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z003
[/TD]
[TD]Entertainment Expense
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z003
[/TD]
[TD]Entertainment Expense
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Z004
[/TD]
[TD]Other Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z000
[/TD]
[TD]Mortgage Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z001
[/TD]
[TD]Mortgage Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z002
[/TD]
[TD]Utilities Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z003
[/TD]
[TD]Interest Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z003
[/TD]
[TD]Interest Expense
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Z004
[/TD]
[TD]Amortization/Depreciation
[/TD]
[/TR]
</tbody>[/TABLE]


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.