none
Reporting Manager change month on month RRS feed

  • Question

  • Hello community,

    I have a data in the form of Employee IDs and their respective Manager IDs month on month.

    I need to have an output which is a pivot of all unique Employee IDs and any manager change month on month to be flagged against that month. Here is how the data looks like.

    Month-Year        Employee ID

    Supervisor ID

    2018-04-01 10000040 10304548
    2018-04-01 10000042 10935676
    2018-04-01 10000065 10303339
    2018-04-01 10000092 10610767

    Wednesday, November 27, 2019 11:23 PM

Answers

  • Hello

    Here my proposed solution. The program is not looking for the value in the row above but checkes the date... so if there is a change between current managerID and the ManagerID the month before.

    That said ... the date-column has to be a date and has to represent always the first of that month (this logic could be changed).

    My table looks like this.

    Date EmployeeID ManagerID
    01.04.2018 1000040 1000999
    01.05.2018 1000040 1000999
    01.06.2018 1000040 1000998
    01.07.2018 1000040 1000998
    01.04.2018 1000041 1000970
    01.05.2018 1000041 1000970
    01.06.2018 1000041 1000970
    01.07.2018 1000041 1000971

    the query adds a new column to check if there was a change from the last month to the one from the current row. Afterward the result of 1 or 0 is pivoted. Here the query

    let
        Source = Excel.CurrentWorkbook(){[Name="tblEmployeeManagerMonth"]}[Content],
        ChangeType= Table.TransformColumnTypes
            (Source,
            {{"Date", type date}, {"EmployeeID", type text}, {"ManagerID", type text}}
            ),
        AddColumnCheckIfChanges = Table.AddColumn
            (
            ChangeType, 
            "Change in this month", 
            (addcolumn) => 
                try 
                    if 
                        Table.SelectRows
                            (
                            ChangeType, 
                            each ([EmployeeID]= addcolumn[EmployeeID]) and ([Date] = Date.AddMonths(addcolumn[Date], -1))
                            )
                            [ManagerID]{0} = addcolumn[ManagerID] 
                    then 
                    0 
                    else
                    1 
                otherwise
                 0
            ),
        TransformDate = Table.TransformColumns
            (
            AddColumnCheckIfChanges , 
            {{"Date", each Date.MonthName(_)&"-"&Text.From(Date.Year(_))}}
            ),
        DeleteManager = Table.RemoveColumns
            (
            TransformDate,
            {"ManagerID"}
            ),
        PivotDate = Table.Pivot
            (
            DeleteManager, 
            List.Distinct
                (
                DeleteManager[Date]
                ),
            "Date", 
            "Change in this month", 
            List.Sum
            )
    in
        PivotDate

    have fun

    Query it

    Thursday, November 28, 2019 3:10 PM
  • Hi anksetgo123

    If jimmy's proposal solves your problem please Mark as answer (hlink at the bottom)

    An alternative if you have a large table and hit performance issue. Uses ImkeF (@TheBiccountant) function called fnShiftByOneRow below

    fnShiftByOneRow

    // By ImkeF @ thebiccountant.com
    (MyTable as table, MyColumnName as text, optional NewColumnName as text) as table =>
    let
        Source = MyTable,
        AddedColumnName = if NewColumnName = null
                          then "_PREVIOUS_"
                          else NewColumnName,
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),
        Custom1 = Table.ToColumns(Source) & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {AddedColumnName})
    in
        Custom2

    Query code

        Source = YourSourceTable,
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"EmployeeID", Int64.Type}, {"ManagerID", Int64.Type}}),
        /* if Source is already ordered by EmployeeID and Date, remove next step */
        SortedEmplDate = Table.Sort(ChangedTypes,{{"EmployeeID", Order.Ascending}, {"Date", Order.Ascending}}),
        GroupedEmplID = Table.Group(SortedEmplDate, {"EmployeeID"}, {{"GRP_EmplID", each _, type table}}, GroupKind.Local),
        RemovedOtherColumns = Table.SelectColumns(GroupedEmplID,{"GRP_EmplID"}),
        TransformedGroup = Table.TransformColumns(RemovedOtherColumns,
            {"GRP_EmplID", each
                let
                    shifted = fnShiftByOneRow(_,"ManagerID","PreviousMonthMgr")
                in
                    Table.AddColumn(shifted,"Chg_Count", each
                        if ([PreviousMonthMgr] = null) or ([PreviousMonthMgr] = [ManagerID])
                        then 0 else 1, Int64.Type
                    ), type table
            }
        ),
        CombinedGroup = Table.Combine(TransformedGroup[GRP_EmplID]),
        SelectedColumns = Table.SelectColumns(CombinedGroup,{"Date","EmployeeID","Chg_Count"}),
        SortedDate = Table.Sort(SelectedColumns,{{"Date", Order.Ascending}}),
        DateAsText = Table.TransformColumns(SortedDate, {"Date", each Date.ToText(_, "MMM yyyy"), type text}),
        PivotedDate = Table.Pivot(DateAsText, List.Distinct(DateAsText[Date]), "Date", "Chg_Count", List.Sum)
    in
        PivotedDate

    Wednesday, December 4, 2019 7:07 AM

All replies

  • Hi,

    Please, provide more detailed data sample and desired result.

    Thursday, November 28, 2019 10:43 AM
  • Thank you for your response Aleksei.

    I think you are unable to see the column labels in the post above. First column is the month-year of the Employee in which they are active; second column is the unique identifier (employee ID) and third column is the ID of the reporting manager.  

    Manager change implies a change in the Reporting Manager ID of an employee from one month to another. 

    My desired output should look something like below.

    Wherein '1' stands for a manager change in that month.

    Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 Jan-19 Feb-19 Mar-19 Apr-19
    10000040 1 1
    10000042 1 1
    10000065 1 1
    10000092 1 1

    Let me know if you still need more info

    Thursday, November 28, 2019 1:58 PM
  • Please don't mind the formatting of the table above - the '1's can be in any of the months
    Thursday, November 28, 2019 2:39 PM
  • I've seen the column labels, but unfortunately even after your explanation I don't understand the logic, how second table results from first one. In first table I don't see information about "the change in Reporting Manager ID of an employee" (as far as I understand, should be several rows (two, at least) with different managers for specific employee during several months).
    Thursday, November 28, 2019 2:56 PM
  • Hello

    Here my proposed solution. The program is not looking for the value in the row above but checkes the date... so if there is a change between current managerID and the ManagerID the month before.

    That said ... the date-column has to be a date and has to represent always the first of that month (this logic could be changed).

    My table looks like this.

    Date EmployeeID ManagerID
    01.04.2018 1000040 1000999
    01.05.2018 1000040 1000999
    01.06.2018 1000040 1000998
    01.07.2018 1000040 1000998
    01.04.2018 1000041 1000970
    01.05.2018 1000041 1000970
    01.06.2018 1000041 1000970
    01.07.2018 1000041 1000971

    the query adds a new column to check if there was a change from the last month to the one from the current row. Afterward the result of 1 or 0 is pivoted. Here the query

    let
        Source = Excel.CurrentWorkbook(){[Name="tblEmployeeManagerMonth"]}[Content],
        ChangeType= Table.TransformColumnTypes
            (Source,
            {{"Date", type date}, {"EmployeeID", type text}, {"ManagerID", type text}}
            ),
        AddColumnCheckIfChanges = Table.AddColumn
            (
            ChangeType, 
            "Change in this month", 
            (addcolumn) => 
                try 
                    if 
                        Table.SelectRows
                            (
                            ChangeType, 
                            each ([EmployeeID]= addcolumn[EmployeeID]) and ([Date] = Date.AddMonths(addcolumn[Date], -1))
                            )
                            [ManagerID]{0} = addcolumn[ManagerID] 
                    then 
                    0 
                    else
                    1 
                otherwise
                 0
            ),
        TransformDate = Table.TransformColumns
            (
            AddColumnCheckIfChanges , 
            {{"Date", each Date.MonthName(_)&"-"&Text.From(Date.Year(_))}}
            ),
        DeleteManager = Table.RemoveColumns
            (
            TransformDate,
            {"ManagerID"}
            ),
        PivotDate = Table.Pivot
            (
            DeleteManager, 
            List.Distinct
                (
                DeleteManager[Date]
                ),
            "Date", 
            "Change in this month", 
            List.Sum
            )
    in
        PivotDate

    have fun

    Query it

    Thursday, November 28, 2019 3:10 PM
  • Do you want the Employee ID to be under "Dec-17" column or can it be a separate column, called "Employee ID" for example? Also, do you absolutely want changes to be marked with 1 or can the changes be Manager ID?

    Friday, November 29, 2019 7:27 AM
  • Hi anksetgo123

    If jimmy's proposal solves your problem please Mark as answer (hlink at the bottom)

    An alternative if you have a large table and hit performance issue. Uses ImkeF (@TheBiccountant) function called fnShiftByOneRow below

    fnShiftByOneRow

    // By ImkeF @ thebiccountant.com
    (MyTable as table, MyColumnName as text, optional NewColumnName as text) as table =>
    let
        Source = MyTable,
        AddedColumnName = if NewColumnName = null
                          then "_PREVIOUS_"
                          else NewColumnName,
        ShiftedList = {null} &  List.RemoveLastN(Table.Column(Source, MyColumnName),1),
        Custom1 = Table.ToColumns(Source) & {ShiftedList},
        Custom2 = Table.FromColumns(Custom1, Table.ColumnNames(Source) & {AddedColumnName})
    in
        Custom2

    Query code

        Source = YourSourceTable,
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"EmployeeID", Int64.Type}, {"ManagerID", Int64.Type}}),
        /* if Source is already ordered by EmployeeID and Date, remove next step */
        SortedEmplDate = Table.Sort(ChangedTypes,{{"EmployeeID", Order.Ascending}, {"Date", Order.Ascending}}),
        GroupedEmplID = Table.Group(SortedEmplDate, {"EmployeeID"}, {{"GRP_EmplID", each _, type table}}, GroupKind.Local),
        RemovedOtherColumns = Table.SelectColumns(GroupedEmplID,{"GRP_EmplID"}),
        TransformedGroup = Table.TransformColumns(RemovedOtherColumns,
            {"GRP_EmplID", each
                let
                    shifted = fnShiftByOneRow(_,"ManagerID","PreviousMonthMgr")
                in
                    Table.AddColumn(shifted,"Chg_Count", each
                        if ([PreviousMonthMgr] = null) or ([PreviousMonthMgr] = [ManagerID])
                        then 0 else 1, Int64.Type
                    ), type table
            }
        ),
        CombinedGroup = Table.Combine(TransformedGroup[GRP_EmplID]),
        SelectedColumns = Table.SelectColumns(CombinedGroup,{"Date","EmployeeID","Chg_Count"}),
        SortedDate = Table.Sort(SelectedColumns,{{"Date", Order.Ascending}}),
        DateAsText = Table.TransformColumns(SortedDate, {"Date", each Date.ToText(_, "MMM yyyy"), type text}),
        PivotedDate = Table.Pivot(DateAsText, List.Distinct(DateAsText[Date]), "Date", "Chg_Count", List.Sum)
    in
        PivotedDate

    Wednesday, December 4, 2019 7:07 AM