Organizational Chart Data to Hierarchy Data - table provided

[rhaddad87]

Hello.  I have a dataset that looks like the table below:

Name                      Reports To           Number of Direct Reports

Manager	PerformManager	2
Employee	Manager	0
Employee2	Manager	0
PerformManager	Lynn	1
Lynn		1

In this scenario, the Rank would be:

Lynn: 0

Perform Manager: 1

Manager: 2

Employee: 3

Employee2: 3

and so on and so forth

Is there an easy way Power BI does this or a facny way I can do it in Power Query?  I have about 500 rows to go through with the same information. 

Thank you.

[v-yueyunzh-msft · Accepted solution]

Hi , @rhaddad87 

Here are the steps you can refer to :
(1)We need to convert our table in Power Query Editor, you can put this code in "Advanced Editor" in your Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MS0xPLVLSUQpILUrLL8pFCBgpxepEK7nmFuTkV6amAgUQUgYoUkZY5DBM86nMywNShmBZKAfCjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Reports To" = _t, #"Number of Direct Reports" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Reports To", type text}, {"Number of Direct Reports", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Number of Direct Reports"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Index1" = Table.AddIndexColumn(#"Removed Columns1", "Index", 1, 1, Int64.Type),
    test = Table.ReorderColumns(#"Added Index1",{"Index", "Name", "Reports To"}),
    #"Added Custom" = Table.AddColumn(test, "Custom",  (x)=>if List.IsEmpty(Table.SelectRows(test,(y)=>y[Name]=x[Reports To])[Index]) then null else Table.SelectRows(test,(y)=>y[Name]=x[Reports To])[Index]{0} ),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Reports To"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Index", type text}, {"Custom", type text}})
in
    #"Changed Type1"

The result table is like this:

(2)We can apply data to Power BI Desktop, then we need to click "New Column" to create two calculated columns in Power BI Desktop:

Path = PATH('Table'[Index],'Table'[Custom])

Rank = PATHLENGTH('Table'[Path]) -1

(3)Then we can meet your need  , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rhaddad87]

Wow!!!! This is amazing!  I thought that I would just have to manually do it but you just helped me out so much!  You are a super hero!  Thank you!!!!!

[rhaddad87]

Just following up to this, I got it to work with my dataset that's coming from Dataverse, just had to do some minor tweaks to the query code.  Everything else was super easy.  I can't thank you enough.  Thank you thank you!