Fetch data from same table different rows

[FatherTheWizard]

Hello,

My problem is quite easy but I cannot find a solution. In Excel the green columns would do the the trick for the first part of the problem:

I have in Power Query (BI) the following as a table containing only the yellow columns. With Excel formulas I can quite easily fetch the helper columns (two custom columns + department type) and the result columns (team name, company name, area name). Results columns are the ones that I would like to have but I currently have only one column (row name) which contains department info that can be on different levels (team, company or area). In Excel 'custom columns' are fetched using index match from id & parent id columns. Department type fetched from 'row name' column (if row contains team, company or area). 

How can I do the same in query?

The formulas for the above:

 ID=K column, row name = L etc.

			Formula	Formula	Formula	Formula	Formula	Formula
id	parent id	row name	custom column: parent name	Custom column: parent name 2	Department type	Team name	Company name	Area name
1	2	Team 1	=INDEX(L:L;MATCH(K5;J:J;0))	=INDEX(M:M;MATCH(M5;L:L;0))	=IF(ISNUMBER(SEARCH("team";L5));"Team";IF(ISNUMBER(SEARCH("company";L5));"Company";"Area"))	=IF(O5="Team";L5;"")	=IF(O5="Team";M5;IF(O5="company";L5;""))	=IF(O5="Team";N5;IF(O5="company";M5;L5))
2	3	Company 1	=INDEX(L:L;MATCH(K6;J:J;0))	=INDEX(M:M;MATCH(M6;L:L;0))	=IF(ISNUMBER(SEARCH("team";L6));"Team";IF(ISNUMBER(SEARCH("company";L6));"Company";"Area"))	=IF(O6="Team";L6;"")	=IF(O6="Team";M6;IF(O6="company";L6;""))	=IF(O6="Team";N6;IF(O6="company";M6;L6))
3	4	Area 1	=INDEX(L:L;MATCH(K7;J:J;0))	=INDEX(M:M;MATCH(M7;L:L;0))	=IF(ISNUMBER(SEARCH("team";L7));"Team";IF(ISNUMBER(SEARCH("company";L7));"Company";"Area"))	=IF(O7="Team";L7;"")	=IF(O7="Team";M7;IF(O7="company";L7;""))	=IF(O7="Team";N7;IF(O7="company";M7;L7))

[FatherTheWizard]

Any ideas? Can this be done with measures or new columns or should I do some kind of merging? Any help highly appreciated. I can also provide additional info if needed.

[FatherTheWizard · Accepted solution]

I solved this by myself using simply conditional columns and merge of columns.