Executive minus 1

Power Query
1

Attached is a mock up excel of my data,

The excel has a list of all employees, they are in the column “user”, then there are the managers to the right in a new column. At the very far right of everyone is “Bobby Buns”. He is the CEO of the company. Depending on your level at the company, Bobby may be 5 up from you or 1 up from you. Everyone who reports into Bobby is referred to in our company as the SET members.

My goal:
I want to merge all the manager columns together with a special delimiter, then simply to a split by delimiter (far right) to pull out the CEO, then do another split by delimiter and pull out the SET members (people who report into Bobby). The issue i keep getting is that on the people who are only 1 or 2 away from Bobby they end up having multiple characters at the end of their name so i can’t do a simple split by far right delimiter, i need to add some additional logic and i can’t think of a clean way of doing it.

Thanks
Executive Minus 1.xlsx (16.9 KB)

2

Hi @chad.sharpe,

See if this solves it for you.

  1. Create a list of all Record field values: Record.ToList(_)
  2. Remove blanks: List.RemoveMatchingItems( Record.ToList(_), {null, “”})
  3. Reverse list order: List.Reverse( List.RemoveMatchingItems( Record.ToList(_), {null, “”}))
  4. Extract 2nd item: List.Reverse( List.RemoveMatchingItems( Record.ToList(_), {null, “”})){1}?

image
image1158×170 12.9 KB

I hope this is helpful.

2 Likes
3

@chad.sharpe ,

I interpreted this problem differently than @Melissa (which probably means I’m wrong, but if that’s the case I think I’m at least wrong in an interesting way…). I interpreted that you wanted two measures – one that would identify the CEO, and one that would create a single list of all the people reporting to the CEO at various levels.

First, I took your data and created a formal hierarchy structure in Power Query, resulting in this:

image

Then created the following two measures:

CEO =

 VAR vTable =
    ADDCOLUMNS( 'Table', "@RowCEO", PATHITEM( MAX( 'Table'[Hierarchy] ), 1 ) )
VAR Result =
    FIRSTNONBLANK( SELECTCOLUMNS( vTable, "@Target", [@RowCEO] ), [@Target] )
RETURN
    Result

Set =

VAR Path2 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path2", PATHITEM( 'Table'[Hierarchy], 2 ) )
    )
VAR vPath2 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path2", PATHITEM( 'Table'[Hierarchy], 2 ) )
    )
VAR vPath3 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path3", PATHITEM( 'Table'[Hierarchy], 3 ) )
    )
VAR vPath4 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path4", PATHITEM( 'Table'[Hierarchy], 4 ) )
    )
VAR vPath5 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path5", PATHITEM( 'Table'[Hierarchy], 5 ) )
    )
VAR vPath6 =
    DISTINCT(
        SELECTCOLUMNS( 'Table', "@Path6", PATHITEM( 'Table'[Hierarchy], 6 ) )
    )
VAR Stack =
    DISTINCT( UNION( vPath2, vPath3, vPath4, vPath5, vPath6 ) )
VAR Result =
    CONCATENATEX( Stack, [@Path2], UNICHAR( 10 ), [@Path2], ASC )
RETURN
    Result

All put together, it looks like this:

image
image806×526 18.3 KB

Interesting problem either way.

I hope this is at least marginally useful.

– Brian
eDNA Forum - CEO Hierarchy Solution.pbix (23.6 KB)

P.S. Enjoyed your anonymized names here. The truly useful measure would be the one that returned PATHITEM results such that it put Chad in Billy Madison’s supervisory chain…

chad

billy

  • Brian
1 Like
4

The output is correct :slight_smile:
I’m not familiar with the steps, I’ll play around with it today.

5

lolol… love the reference…Melissa had the right idea. I’ll still take a look at the solution, everything answer has something new for me to pick up on.

2 Likes
6

Hi @chad.sharpe, did the response provided by @BrianJ and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

7

Hi @chad.sharpe due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.