Forum Discussion

casar's avatar
casar
Regular Visitor
1 year ago
Solved

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

Hi everybody: I am looking for an automatic transformation of the following excel table containing FTE (Full-time employee) by Position.  Area Position Code Company Jan-26 Feb-26 Mar-26 ...
Need Help
Tips and Tricks
Tutorial Requests
  • mromain's avatar
    mromain
    1 year ago

    Hello everyone,

    casar:
    Another solution below

    let
    Source = #table({"Area", "Position", "Code", "Company", "Jan-26", "Feb-26", "Mar-26", "Apr-26", "May-26", "Jun-26", "Jul-26", "Aug-26", "Sep-26", "Oct-26", "Nov-26", "Dec-26"},
        {
            {"SC", "Purchasing Officer", "SCO", "ABC", null, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1},
            {"Support", "Process Improvement Specialist", "SME", "ABC", null, null, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3},
            {"Engineering", "Engineer", "ENG", "XYZ", null, 0.5, 0.5, 0.5, 0.5, 1, 1, 1, 1, 2, 2, 2},
            {"PM", "Project Manager", "PMM", "XYZ", 1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3},
            {"test", "test", "test", "test", null, 1, 1, 1, 1, null, 2, 2.15, 2, 3, 3, 3.4}
        }),
    DispatchFte = 
        let
            ListDateColumns = List.Buffer(List.Select(Table.ColumnNames(Source), each not (try Date.FromText(_, "en-US"))[HasError])),
            fnChangeRecors = (r as record) as record =>
                let
                    listValues = Record.ToList(Record.SelectFields(r, ListDateColumns)),
                    tableValues = 
                        let 
                            fnConvertValue = (v) => List.Transform({0 .. Number.RoundUp(List.Max(listValues))-1}, each if v = null then null else let fte = List.Max({List.Min({1, v-_}), 0}) in if fte = 0 then null else fte)
                        in 
                            Table.FromColumns(List.Transform(listValues, fnConvertValue), ListDateColumns)
                in
                    Record.AddField(Record.RemoveFields(r, ListDateColumns), "data", tableValues),
            ConvertFte = Table.FromRecords(Table.TransformRows(Source, fnChangeRecors))
        in
            Table.ExpandTableColumn(ConvertFte, "data", ListDateColumns, ListDateColumns)
in
    DispatchFte
Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

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