Power Query Conditional Column Logic for Hierarchies

[derekmac]

#"Added Custom" = Table.AddColumn(#"Removed Columns", "Level 1", each if [Manager4_Name]= null then [Manager3_Name] else [Manager4_Name]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Level1a", each if [Level 1] = null then [Manager2_Name] else [Level 1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Level1b", each if [Level1a] = null then [Manager1_Name] else [Level1a]),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Level 1c", each if [Level1b] = null then [ReportingTo] else [Level1b])

Hi All
I have added 4 Steps above to create a Column with highest non null Value across 4 Columns

Manager4_Name     Manager3_Name   Manager2_Name Manager1_Name    ReportingTo

John Smith

null              David Smith     Pete Smith

                                                                                    
null              null            Rob Smith    Jackie Smith     Geraint Smith

I woul like to get

Level 1

John Smith
David Smith                                                                                    Rob Smith

My Steps above appear to achieve this, but can this be done in one Step? 

[wdx223_Daniel]

=List.Skip(Record.ToList(_),each _=null){0}?

=List.Skip(Record.ToList(_),each _=null){1}?

[mlsx4]

Hi @derekmac 

Yes, you can do it using this:

= Table.AddColumn(#"Removed Columns", "Level 1", each [Manager4_Name]??[Manager3_Name]??[Manager2_Name]??[Manager1_Name]??[#" ReportingTo"])

[derekmac]

Thanks a lot

How would I replicate this for Level 2 so in the example above I get Pete Smith from the middle line? and Jackie Smith in the 3rd line?

[mlsx4 · Accepted solution]

Hi @derekmac 

I suppose it will be a matter of adding a couple of "ifs". Try it: 

= Table.AddColumn(#"Removed Columns", "Level 2", each if [Manager4_Name]<>null then [Manager3_Name]??[Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager3_Name]<>null then [Manager2_Name]??[Manager1_Name]??[#" ReportingTo"] else if [Manager2_Name]<>null then [Manager1_Name]??[#" ReportingTo"] else if [Manager1_Name]<>null then [#" ReportingTo"] else null)