Generating rows based on values in several columns - Ungrouping values in a time series dataset

Hey casar ,

You're looking to ungroup a time series dataset so that each full-time employee (FTE) is represented by a separate row, with a value of 1 spread across the months according to the original FTE counts essentially "exploding" the table vertically by max FTE and allocating 1s appropriately.

Power Query Transformation Steps

1. Load the data table into Power Query.
2. Unpivot the months (columns like Jan-26 to Dec-26).
3. Round FTE values up to the nearest integer, treating anything >0 and <1 as 1.
4. Determine max FTE per [Area, Position, Code, Company] group.
5. Duplicate each row up to the max FTE, giving you one row per FTE slot.
6. Fill in 1s only if current slot (row index) is ≤ original FTE for that month.
7. Pivot months back to columns.

Sample M code for Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Area", "Position", "Code", "Company"}, "Month", "FTE"),
    ReplaceNull = Table.TransformColumns(Unpivoted, {{"FTE", each if _ = null then 0 else _, type number}}),
    Rounded = Table.TransformColumns(ReplaceNull, {{"FTE", each Number.RoundUp(_, 0), type number}}),
    AddMaxFTE = Table.Group(Rounded, {"Area", "Position", "Code", "Company"}, {
        {"AllData", each _, type table},
        {"MaxFTE", each List.Max([FTE]), Int64.Type}
    }),
    Expanded = Table.ExpandTableColumn(AddMaxFTE, "AllData", {"Month", "FTE"}),
    AddIndexList = Table.AddColumn(Expanded, "IndexList", each List.Numbers(1, [MaxFTE]), type list),
    ExpandIndex = Table.ExpandListColumn(AddIndexList, "IndexList"),
    AddOnes = Table.AddColumn(ExpandIndex, "Value", each if [IndexList] <= [FTE] then 1 else null),
    RemovedExtras = Table.RemoveColumns(AddOnes, {"FTE", "MaxFTE", "IndexList"}),
    PivotBack = Table.Pivot(RemovedExtras, List.Distinct(RemovedExtras[Month]), "Month", "Value", List.Sum),
    Final = Table.SelectColumns(PivotBack, {"Area", "Position", "Code", "Company"} & List.Sort(List.Distinct(Unpivoted[Month])))
in
    Final

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam