Hierarchy Transformations - Level to Title

[Anonymous]

To the experts.. I'm having some trouble conceptualizing my Power Query transformation steps for the current challenge I have. I have a table that list a project, with an employee that's responsible for the project and then their tiered-leaders (Leader #1, Leader#2, etc). The tiering table includes names and titles.

What I need though is to transform that tiered table into a table that shows the projects with its responsible person and then their leaders by TITLE, not position number. I know there will be some form of any or all of PIVOT, UNPIVOT, TRANSPOSE, and the like, I just can't quite get my head around the steps. Attached is a snapshot of my initial table on top and my desired end result. Any help would be greatly appreciated.

PS: if it wasn't clear, I want to do these transformations strictly in M Code (Power Query).

Thank you in advance,
-Nate

Project

Person

Person ID

LEADER 1 Name

LEADER 1 ID

LEADER 1 Title

LEADER 2 Name

LEADER 2 ID

LEADER 2 Title

LEADER 3 Name

LEADER 3 ID

LEADER 3 Title

LEADER 4 Name

LEADER 4 ID

LEADER 4 Title

T555555

Joe

1234

John

2345

Director

Jim

3456

Vice President

Jill

4567

President

Jeff

5678

CEO

T777777

Paul

4321

Carl

5432

Supervisor

Gabe

6543

Manager

Jose

7654

Director

Todd

8765

Vice President

T999999

Joe

7408

Jack

8519

Manager

Stacey

9630

Director

Lindsay

10741

Vice President

Lauren

11852

President

T122222

Bill

10495

Sandy

11606

Director

Doug

12717

Vice President

Landon

13828

President

Joseph

14939

CEO

Title

Title

Title

Title

Title

Title

Project

Person

Person ID

Supervisor

SupervisorID

Manager

ManagerID

Director

DirectorID

Vice President

Vice PresidentID

President

PresidentID

CEO

CEOID

T555555

Joe

1234

John

2345

Jim

3456

Jill

4567

Jeff

5678

T777777

Paul

4321

Carl

5432

Gabe

6543

Jose

7654

Todd

8765

T999999

Joe

7408

Jack

8519

Stacey

9630

Lindsay

10741

Lauren

11852

T122222

Bill

10495

Sandy

11606

Doug

12717

Landon

13828

Joseph

14939

[Anonymous · Accepted solution]

UPDATE: After doing some transformation to get my titles normalized, like I showed in the example, I solved this by just creating custom columns for each title looking at all of the leaders titles and placing their name in the proper column if their normalized title matched the column title I needed. Here are some advanced editor examples... pretty easy in reality.

 Director = Table.AddColumn(VP, "DIRECTOR", each if [#"SUP_1_TITLE-Groomed"] = "DIRECTOR" then [SUP_1_Name] else if [#"SUP_2_TITLE-Groomed#(cr)#(lf)"] = "DIRECTOR" then [SUP_2_Name] else if [#"SUP_3_TITLE-Groomed"] = "DIRECTOR" then [SUP_3_Name] else if [#"SUP_4#(cr)#(lf)_TITLE-Groomed"] = "DIRECTOR" then [SUP_4_Name] else "TBD"),
 
Manager = Table.AddColumn(Director, "MANAGER", each if [#"SUP_1_TITLE-Groomed"] = "MANAGER" then [SUP_1_Name] else if [#"SUP_2_TITLE-Groomed#(cr)#(lf)"] = "MANAGER" then [SUP_2_Name] else if [#"SUP_3_TITLE-Groomed"] = "MANAGER" then [SUP_3_Name] else if [#"SUP_4#(cr)#(lf)_TITLE-Groomed"] = "MANAGER" then [SUP_4_Name] else "TBD"),

[ziying35 · Accepted solution]

@Anonymous 

try this:

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("hdRPb4IwGAbwr0J69kDLn4K3TcyyhW1kml3MDh1UZcPWFFhiln33FRaxllfXm09eH82P8q6+UabkB88bNEXLoD9ogjKuail09CD58NG5T9AUE8+foHR+k8xfHOw8sR3vx7YCGXE3qQcDI1qWTdWNJqXSvybVaZwMLeXOTLsS3REa0bHktcy5kylelwUXzelL3qmqqsy469JV1IiOXUCNP9Tw9dqMuxrdEhnRsWY2f0Y/kzNO2h+TM2Ntde7pewSPPWdMVbZnoEcBz0W75+qrrEHRO/bObdJQFwGkj0ywDVeQpay5bUl1C2A5frgD5VIWhU0Z6RqA0nq6lmrcn6uXlPpuBFxSln/aqFGAYwB1hDGILhqW84NtGoeeC5iOOQbUtBRFzQ62K3apjwHYSxd+4E1Zq7iwgTGOAgIIX8TFpDsm7u3fm2SuANePgzHvgoniYPtiHLohAHxlCySy3di+mFBMAeB/90Cq/5QUI2UvIhGgfHUV6Ldgvx0B+7EXA8D9Nnj7BQ==", BinaryEncoding.Base64),Compression.Deflate))),
    fd = Table.ColumnNames(Source),
   
    fx = (rec)=>
         let fnField = (txt)=> Record.Field(rec, List.Select(Record.FieldNames(rec), each Text.EndsWith(_, txt)){0})
         in  Record.AddField(Record.AddField([],fnField("Title"),fnField("Name")),fnField("Title")&"ID",fnField("ID")),
         
    recs = Table.TransformRows(
              Source, 
              (r)=> Record.SelectFields(r, List.FirstN(fd,3))&
                    Record.Combine(List.Transform(List.Split(List.Skip(fd,3),3), each fx(Record.SelectFields(r, _))))),
    toTbl = Table.FromRecords(recs, Record.FieldNames(Record.Combine(recs)), 2),
    ref_order = {"S", "M", "D", "V", "P", "C"},
    nfd = List.Sort(
             List.Skip(Table.ColumnNames(toTbl), 3), 
             {{each List.PositionOf(ref_order, _, 0, (x,y)=>Text.StartsWith(y,x)), 0}, {Text.Length, 0}}
          ),
    result = Table.ReorderColumns(toTbl, nfd)
in
    result

[Anonymous]

I just applied this in a blank query and am astonished at how simple the transformations are. I don't know M well enought to know what you did here but it's definitely simpler than what I came up with

[ziying35]

@Anonymous 

In short, my code is to take the value of each person's job field from the three information fields, and use this value to rename all the information fields belonging to him, while deleting his original job field, and finally sort all the fields of the table according to the rank of the position.

My code used in some of the syntax in the official documentation which can not necessarily be found, see my code requires some time and experience, I hope you help!

[Anonymous · Accepted solution]

UPDATE: After doing some transformation to get my titles normalized, like I showed in the example, I solved this by just creating custom columns for each title looking at all of the leaders titles and placing their name in the proper column if their normalized title matched the column title I needed. Here are some advanced editor examples... pretty easy in reality.

 Director = Table.AddColumn(VP, "DIRECTOR", each if [#"SUP_1_TITLE-Groomed"] = "DIRECTOR" then [SUP_1_Name] else if [#"SUP_2_TITLE-Groomed#(cr)#(lf)"] = "DIRECTOR" then [SUP_2_Name] else if [#"SUP_3_TITLE-Groomed"] = "DIRECTOR" then [SUP_3_Name] else if [#"SUP_4#(cr)#(lf)_TITLE-Groomed"] = "DIRECTOR" then [SUP_4_Name] else "TBD"),
 
Manager = Table.AddColumn(Director, "MANAGER", each if [#"SUP_1_TITLE-Groomed"] = "MANAGER" then [SUP_1_Name] else if [#"SUP_2_TITLE-Groomed#(cr)#(lf)"] = "MANAGER" then [SUP_2_Name] else if [#"SUP_3_TITLE-Groomed"] = "MANAGER" then [SUP_3_Name] else if [#"SUP_4#(cr)#(lf)_TITLE-Groomed"] = "MANAGER" then [SUP_4_Name] else "TBD"),

[Icey]

Hi @Anonymous ,

Glad to hear that the problem has been solved. Please accept your reply as the solution.

In addition, I can't reproduce your results. Could you share us more details or just a dummy .pbix file? I really want to know how you did it. Your contribution is highly appreciated.😀

Best regards

Icey

[ziying35]

Hi, @Anonymous 

Your problem shouldn't be too hard to solve, but I'm too slow to get this data into the table from the screenshot you provided. Can you upload your example table to the cloud, and then share the link here?

[Anonymous]

Thank you for your response. I added the table-concepts to the post. I think they can be copy and pasted from there