Forum Discussion

AGuero's avatar
AGuero
Frequent Visitor
8 years ago
Solved

Second to last non blank value in an array?

I have a chart with employees and reporting relationships and am trying to do some analysis by the VP of each department.  My data looks like this:   Department  Employee     First Manager     Seco...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago

    Could try a big nested if, something like:

     

    if([fourth manager]=null,(similar logic checking the third manager),third manager)

     

    It'll look at each column in turn, and when it finds one that isn't blank, returns the previous column

MarkS's avatar
MarkS
Icon for Resolver IV rankResolver IV
8 years ago

Hi AGuero

  In the query editor, could you filter the First Manager column=President?  That should give you the VP of that department as the only employee in the department.  To get to your desired result just delete the other columns an Rename the employee column to VP.

 

 

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

In Power Query, just select the rows where First Manager <> null and Second Manager = null

 

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each [First Manager] <> null and [Second Manager] = null),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Department", "Employee"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Employee", "VP"}})
in
    #"Renamed Columns"