Forum Discussion
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 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!
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
7 Replies
jthomsonSolution Sage
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
- Abduvali
Skilled Sharer
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
- MarcelBeug
Community Champion
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" 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] )
