Combine all information from upper hierarchy levels into 1 table

[Teun1990]

Hi,

I made a visual representation of what i would like to achieve in Power Query Editor, as can be found below.

Since in the current situation the dataset is a lot larger (& 'deeper' in hierachy), I would like to avoid using multiple merges over & over again (for performance issues)

Does anyone have any idea? 

Kind Regards,

Teun

[lucadelicio]

OPTION 1
You can try to build a table with n join like the n level of child.

So you obtain a table like this for every child:

Child	Parent3	Parent2	Parent1
7	5	3	1
1	-	-	-

Then you can:
CONCAT the lookup value of all your level.
And you obtain a string result like this for the child 7:
(g); (e); (c); (a)
for child 1:
(a)

OPTION 2
To have the desire result you have to built a table that contains all the combination:

Child	Parent	Level
7	7	4
7	5	3
7	3	2
7	1	1

Then you can create the relation beetwen the parent column and the skill table.
And when on pbi ypu create the result table you have to put the child and the skill and you receive what you want.

I hope will help you.
If yes mark it as solutions.
Ciao!

[Teun1990]

Hi lucadelicio, 

Tnx voor your reply, I found a solution! However I wonder if it can be more efficient. I have to do like 12x merges now..

My startpoint is a child + parent1 column. Next i'll do a merge on the same tabel, lookup Parent1 in Child column, expand parent1 to create Parent 2). I repeat this untill Parent 6 to create below view. 

Next, I'll do a merge on Child with another table to find the compentence for the child. + repeat this 7 times ( 7 merges) do this for all parents. Next i'll Unpivot columns & remove duplicates to get my disired view :). 

Do you have any final idea to make this more efficient ? 

Kind Regards,

Teun 

[lucadelicio · Accepted solution]

Hi ,

try to built the second option i wrote.

I think you can unpivot after you have all the parent column for every id (your first table).

So you'll not have to do the merge for the skill, but you create the relationship with the level details.

Let me know.

[Teun1990]

Tnx, it works!

[lucadelicio]

happy to help you