PowerBI Creating a matrix with text values

[FabiNeed]

I am facing a difficult challenge this time. I got two table from which I want to construct a matrix.

Table 1 (called "Entity") contains a parent-child hierachy and looks like this:

Table 2 (called "Values") has already been unpivoted and contains values for one entity:

The key both tables have in common is the column "EntityKey".

The aim is to create a matrix. "level1", "level2" and "level3" from table 1 should be placed in the rows, "attribute" from table 2 in the columns and "values" from table 2 should be taken as values. The result should look like this:

When displayed in PowerBI, the result looks like this at the moment (see arrows for the problems):

 I uploaded a PowerBI file, where you can see the current status: PowerBI file

As you can see, there are multiple problems, which I am not able to fix:

• There should be no empty rows
• For each level of aggregation, a value should be visible even if it is fully expanded. This value can be different than the levels at a lower level. E.g.: Value for D2: "middle", while the value for I2 within D2 "high"

Any ideas, how I can achieve this? Happy for any advice!

[NickolajJessen]

If you add Table format of your data in stead of screen shots it's easier to copy paste into Power Bi and find a soloution for you 🙂 

[MrSujay]

HI @FabiNeed 

Could you please add the exact expected output? I am getting below output is correct?

[FabiNeed]

Hi @MrSujay 

thanks for your solution. The only thing which is missing is the hirachy which allows to expand or extent items. The expected output should look like this:

[MrSujay]

@FabiNeed  Please upload both source file too if possible