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
burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi casar ,

 

Thank you for providing a detailed explanation and sample tables – that makes your scenario much clearer.

To achieve the output you’re looking for (expanding rows for each position based on FTE values across the time series columns), you can use Power Query in Power BI or Excel to automate this transformation. Here’s a step-by-step approach:

1. Unpivot Your Data

  • In Power Query, select all columns except your time series columns (the monthly columns).
  • Use the “Unpivot Columns” feature to transform your wide table into a long format, where each row represents a single FTE value for a position and month.

2. Expand Rows Based on FTE Count

  • Add a custom column that generates a list according to the FTE value for each row. For values with decimals < 1, use Number.RoundUp([Value],0) to ensure values like 0.5 become 1.
  • Use the “Expand” feature to duplicate the row as many times as the (rounded up) FTE value for that period.

Example Power Query (M) code for a custom column:

m
 
= List.Repeat({[Area], [Position], [Code], [Company], [Month]}, Number.RoundUp([FTE]))

Then expand this list into new rows.

3. Clean Up and Re-Pivot if Needed

  • Once expanded, you can sort or group the data as required.
  • If you want your output in a wide format again, you can use the “Pivot Column” feature.

Key Points:

  • This approach makes sure that, for each period, the number of rows per position matches the (rounded up) FTE value, just like your output sample.
  • For decimals less than 1, rounding up ensures at least one row is produced, as you described for the Engineer row.

References:

If you need a sample Power Query script or more detailed steps, let me know what tool (Excel or Power BI) you’re using, and I’ll be happy to provide a tailored example.

If this helps, please consider giving kudos and marking this as a solution for others in the community.

Thank you!
translation and formatting supported by AI