split, process and recombine data

Your dataset is silent on the actual FTE values so I will make an assumption that the FTE values can be related to an employee via Employee ID and are available in an external table. 

With that assumption in place, you can get your desired result by selecting the 'Department', 'Prev Row Department', and 'Employee ID' columns and then unpivoting based on 'Department' and 'Prev Row Department'.

From there you can merge in the FTE values from the external table and then turn the 'Prev Row Department' rows negative.

 

Here is a sample code, 

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "hdJdb4IwFAbgv3LCtQmFgs5LP2aMHwuZizfGi6rH0Qg0Aczifv1aZmudxd215PDkPT1ns/FI4HW8mUgLWOW8TuWFEp90/ZCEXXl5g6k4VyhPU3MKqE+oLgj7pnrbkVyoOFbgHRfp6oVB5rAUonRykcVRxWFV8T37FUGRgfXHEtYcv+RhCCNR1+zzwVQJqGVG146Lyy2kFGNdn2jxI0VIWFlleHGZsWXGTc5vtsPMjQ5M56/uh/wrqm8znrs103QCE5Ed/rN6zVBOLUO5RXtvn7E9lJem2db3Gz+Gi56E6181fsTSHXAIE1bmz3fQyheQpt39iaN7qUeGWemorTu9/QE=", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let 
            _t = ((type nullable text) meta [Serialized.Text = true]) 
        in 
            type table [#"Employee ID" = _t, #"Full Name" = _t, #"Effective date" = _t, Department = _t, #"Prev Row Department" = _t, #"Prev Row Effective date" = _t, #"Prev Row End effective date" = _t]
    ),
    initial_type_set = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Employee ID", Int64.Type}, {"Full Name", type text}, {"Effective date", type text}, {"Department", type text}, {"Prev Row Department", type text}, {"Prev Row Effective date", type text}, {"Prev Row End effective date", type text}
        }
    ),
    unpivot_selected = 
    Table.Unpivot(
        Table.SelectColumns(
            initial_type_set, 
            {"Department", "Prev Row Department", "Employee ID"}
        ), 
        {"Department", "Prev Row Department"}, 
        "Attribute", 
        "Value"
    ),
    merge_fte = 
    Table.NestedJoin(
        unpivot_selected, 
        {"Employee ID"}, 
        fteTable, 
        {"Employee ID"}, 
        "fteTable", 
        JoinKind.LeftOuter
    ),
    expand_fte = 
    Table.ExpandTableColumn(
        merge_fte, 
        "fteTable", 
        {"FTE"}, 
        {"FTE"}
    ),
    set_negatives = 
    Table.ReplaceValue(
        expand_fte, 
        each [FTE], 
        each 
            if [Attribute] = "Prev Row Department" 
                then -[FTE] 
                else [FTE], 
        Replacer.ReplaceValue, 
        {"FTE"}
    ),
    remove_columns = 
    Table.RemoveColumns(
        set_negatives,
        {"Employee ID", "Attribute"}
    ),
    final_type_set = 
    Table.TransformColumnTypes(
        remove_columns,
        {
            {"FTE", type number}
        }
    ),
    group_rows = 
    Table.Group(
        final_type_set, 
        {"Value"}, 
        {
            {"FTE", each List.Sum([FTE]), type nullable number}
        }
    ),
    rename_value = 
    Table.RenameColumns(
        group_rows,
        {
            {"Value", "Department"}
        }
    )
in
    rename_value

 

I used the following code to create FTE values based on the example data you provided.

let
    Source = 
    #table(
        type table [#"Employee ID"=nullable number, FTE=nullable number],
        {
            {1,.5},
            {2,1},
            {3,1},
            {4,.8},
            {5,1},
            {6,1},
            {7,.66},
            {8,1},
            {9,1},
            {10,1}
        }
    )
in
    Source

 

To end up with the result. (Note: I grouped by 'Department', summing the FTE.)