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.)
Proud to be a Super User! |  |
Helpful resources
New to Fabric Survey
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
Power BI DataViz World Championships - June 2026
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Join our Fabric User Panel
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Power BI Community Update - March 2026
Check out the March 2026 Power BI update to learn about new features.
