Consolidation Index Key using powerquery

[Sonova]

I have a workbook which has data for different projects. Each project has categories and monthly data, using powerquery an index column was created in order to define the relationship between the monthly data and categories.

https://www.dropbox.com/s/l5b3oes2ovg092n/datamodel.PNG?dl=0

My Steps:

1. Create a categorykey with index
2. Categories merged with categorykey
3. Monthly data merged with category key
4. Powerpivot relationship beetween categories and monthly data

Most of the categories are generic however some projects have additional categories or less thus the index numbers will never be the same across all the projects for their respective categories.


My issue now is that I am trying to create a consolidation of the data for all the projects . My thought process was to create an appended Category table(powerquery) and remove any duplicate categories.

However I can not create a relationship of my appended categories with my individual project categories due to duplicate index numbers and If I create a relationship using a custom index (Index1.1) column to my individual project table categories, the index numbers will not match.
https://www.dropbox.com/s/5sfsu7p4l932zqd/Appendedcategorykeys.PNG?dl=0

Example below.

If I create a consolidated pivot table my revenue for an individual projects(custom index as key) it is shown under subcontracting (34).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[TD]Appended Index[/TD]
[TD]Index.1(Custom Index)[/TD]
[/TR]
[TR]
[TD]8Revenue[/TD]
[TD]34[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]5Subcontracting[/TD]
[TD]34[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

https://www.dropbox.com/s/akjdcqi62k0w7yz/J56FinalModelV4.xlsx?dl=0