Second to last non blank value in an array?

[AGuero]

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     Second Manager     Third Manager     Fourth Manager

Dept 1           Employee 1   Employee 2          Employee 3                VP1                       President

Dept 1           Employee 2    Employee 3         VP 1                            President

Dept 1           VP 1                President

Dept 2           Employee 4    Employee 5         Employee 6                 VP 2                     President

Dept 2           Employee 5    Employee 6         VP 2                             President

Dept 2           Employee 6    VP 2                     President

Dept 2           VP 2                President

I want to end up with this:

Dept     VP

Dept 1   VP1

Dept 2   VP2

Please help and thanks!

[MarkS]

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.

[AGuero]

MarkS,

That would work in my simple example, but in reality there are cases where an employee is in a different department than his/her VP. 

[Zubair_Muhammad]

Hi @AGuero

How about a DAX Measure

Dept_VP =
LASTNONBLANK ( VALUES ( TableName[Employee] ), TableName[Employee] )

[AGuero]

Thanks Everyone!

I wrote a long, nexted IF statement that worked

IF(Table[Fourth Level Manager]<>"" && Table[Fourth Level Manager] <> "President", Table[Fourth Level Manager], IF(Table[Third Level Manager]<>""&&Table[Third Level Manager]<>"President",Table[Third Level Manager],IF......

[MarcelBeug]

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"

[Abduvali]

Hi @AGuero,

You need to use Unpivot and then use Group by functions:

Unpivot and Group By Functions in Power BI Desktop

Hope this helps.

Regards

Abduvali

[jthomson · Accepted solution]

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