split, process and recombine data

[AndrewPF]

I have some [anonymised] data - 10 rows in total - which looks like this: 

Employee ID	Full Name	Effective date	Department	Prev Row Department	Prev Row Effective date	Prev Row End effective date
01	John Smith	30/06/2026	N House	H House	13/03/2026	29/06/2026
02	Jane Smith	30/04/2026	L House	K Moore	13/03/2026	29/04/2026
03	Jessica Smith	31/03/2026	M View	B Cottage	13/03/2026	30/03/2026
04	Johnny Smith	31/05/2026	P View	The Parsley	13/03/2026	30/05/2026
05	Jezabel Smith	31/05/2026	A House	E House	13/03/2026	30/05/2026
06	Jim Smith	31/05/2026	M View	P Fold	13/03/2026	30/05/2026
07	Jake Smith	30/04/2026	A House	R House	13/03/2026	29/04/2026
08	Jenny Smith	31/05/2026	D View	P Fold	14/03/2026	30/05/2026
09	Jennifer Smith	30/04/2026	B Farm	H House	13/03/2026	29/04/2026
10	Jackie Smith	30/06/2026	C House	S View	13/03/2026	29/06/2026

representing staff moves from one location to another. 

The "Department" therefore represents "Loaned To", and the "Prev Row Department" represents "Borrowed From". 

I need my final dataset to be 20 rows and look something like this: 

Department	FTE %
N House	50
L House	100
M View	100
P View	80
A House	100
M View	100
A House	66
D View	100
B Farm	100
C House	100
H House	-50
K Moore	-100
B Cottage	-100
The Parsley	-80
E House	-100
P Fold	-100
R House	-66
P Fold	-100
H House	-100
S View	-100

which I then need to summarise at home level (but that bit's easy). 

What's the best way of transposing the data? 

[jgeddes]

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.)

[AndrewPF]

When a member of staff (temporarily) moves from place A to place B, that represents "loaned to", or minus, from place A and a "borrowed from", or plus, in place B.  Therefore, if that staff member is on a part-time contract, and has an FTE of 80% of standard then, in the summary, place A would be 0.80 (80%) down, and place B would be 0.80 (80%) up. 

[AnkitKukreja]

Hi! @AndrewPF 

You might have to share your logic around it? How are you coming to the expected numbers so I can have a look at it.