employee hierarchy i need add column parent (with departmentcode & rank)

[theopa]

hi all!

i need addcolumn with parent index for make hierarchy chart with employee

i have two table. which one is about employee, and department code.

employee table have employee name / index / department code / upper department code / ranks(president : 1, executive managing director : 2 ,Managing Director :3 ..... and so on) 

index is that i add all (by sorting with rank )

and department table have departmentcode /  department name / upper departmentcode / upper departmentname

like just 

departmentcode /  department name / upper departmentcode / upper departmentname

C000  / sosomart / 0 / 

200 / salesteam1 / 190 / salesteam

210 / salesteam2 / 190 / salesteam

190 / salesteam / C000 / sosomart

300 / marketingteam1 /  290 / marketingteam

301 / supergroup /300 / marketingteam1 

310 / marketingteam2 /  290 / marketingteam

290 / marketingteam / C000 / sosomart

there are no column with who is team leader,

so it need just add that grouping department and add parent with rank (who is small ranks)

i think this code is best on my brain, but i can't fill the department leaders parents

is there some solution with it?

    rankedTable = Table.Group(
        removeRankColumn, 
        {"departmentcode"}, 
        {{"rankcolumn", each 
            let
                indexedTable = Table.AddIndexColumn(Table.Sort(_, {{"rank", Order.Ascending}}), "rankcolumn", 1, 1, Int64.Type),
                
                minRankIndex = List.Min(indexedTable[rankcolumn]),
                
                newColumn = Table.AddColumn(indexedTable, "parent", each 
                    if [rankcolumn] = minRankIndex then null else indexedTable{0}[index]
                )
            in
                newColumn
        }}
    ),


    expand = Table.ExpandTableColumn(rankedTable, "departmentcode",{"name","rank","departmentcode","upperdepartmentcode"....."parent"}),

[lbendlin · Accepted solution]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[theopa]

thank you for your answer

but i have to make this with my customer's data