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

[casar]

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	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26
SC	Purchasing Officer	SCO	ABC		1	1	1	1	1	1	1	1	1	1	1
Support	Process Improvement Specialist	SME	ABC			1	1	1	2	2	2	2	2	3	3
Engineering	Engineer	ENG	XYZ		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

The name of this table is "data"

To this output 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		1	1	1	1	1	1	1	1	1	1	1
Support	Process Improvement Specialist	SME	ABC			1	1	1	1	1	1	1	1	1	1
Support	Process Improvement Specialist	SME	ABC						1	1	1	1	1	1	1
Support	Process Improvement Specialist	SME	ABC											1	1
Engineering	Engineer	ENG	XYZ		0.5	0.5	0.5	0.5	1	1	1	1	1	1	1
Engineering	Engineer	ENG	XYZ										1	1	1
PM	Project Manager	PMM	XYZ	1	1	1	1	1	1	1	1	1	1	1	1
PM	Project Manager	PMM	XYZ						1	1	1	1	1	1	1
PM	Project Manager	PMM	XYZ										1	1	1

Name of this table is "ouput"

As you can see, I am trying to generate rows based on the FTE values for each position. Example:

1. On the first row of the table "data": Purchasing officer in the table "ouput" shows only one row as there are only 1 FTE accross the time series.

2. On the second row of the table "data": Process Improvement Specialist. The table "ouput" shows three rows as there is max value of 3 FTE in the time series for this position. However, when generating the rows, they only must have 1 FTE in each row (not aggregated data). As a sanity check, both tables should add up the same totoal FTE per each month (column). The output table is only expanding the original dataset into a more granular level.

3. In regards to the decimals less than 1 (0.5 for example), the value should be considered as a 1 (or equivalent to 1) and output only one row. This is the Engineer row in the dataset.

I was able to generate the rows per position based on a maximum value in the time series, however I', having a hard time to allocate the "ones" accross the time in the output dataset.

Thanks for your help

[p45cal · Accepted solution]

I have responded at the site you cross posted at:

https://chandoo.org/forum/threads/generating-rows-based-on-values-in-several-columns-ungrouping-valu...

[casar]

Hi Bea,

Beautiful solution! the script works correctly. 

Thanks for your help.

[BeaBF]

@casar Hi! mine? if so, please accept the answer as solution! Thx you

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[mromain · Accepted solution]

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

[casar]

Hi mromain,

Thanks for your answer; The code works well and keeps the decimal value as 0.5 until it becomes "ones"!

[p45cal · Accepted solution]

I have responded at the site you cross posted at:

https://chandoo.org/forum/threads/generating-rows-based-on-values-in-several-columns-ungrouping-valu...

[casar]

Hi p45cal,

Your solution exceeds my expectations!

Thank you so much!

[AlienSx]

@p45cal, I've responded over here as well

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    to_list = List.TransformMany(
        Table.ToList(Source, (x) => x), 
        (x) => List.Zip(
            List.Transform(
                List.Skip(x, 4), 
                (w) => List.Repeat(
                    {if w is null then null else List.Min({w, 1})}, 
                    List.Max({w, 1})
                )
            )
        ),
        (x, y) => List.FirstN(x, 4) & y
    ),
    result = Table.FromList(to_list, (x) => x, Value.Type(Source))
in
    result

[slorin]

Hi @casar 

=Table.Combine(
  List.Transform(
    Table.ToRows(Data), 
    (x) =>
      Table.FromColumns(
        List.Transform(
          x, 
          each 
            if Value.Is(_, type number) then
              List.Repeat({List.Min({_, 1})}, Number.RoundUp(_))
            else
              List.Repeat({_}, List.Last(x))), 
         Table.ColumnNames(Data))))

Stéphane

[casar]

Hi Stephane,

Thanks for your effort and time.

[v-kpoloju-msft]

Hi @casar,
Thank you for reaching out to the Microsoft fabric community forum. 

Has your issue been resolved? If the response provided by the community members @Nasif_Azam, @burakkaragoz, @SundarRaj, @BeaBF, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[Nasif_Azam]

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

[burakkaragoz]

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:

• Unpivot columns in Power Query
• List.Repeat function in Power Query
• Pivot and unpivot with Power Query

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!

[SundarRaj]

Hi @casar , here's the solution as per the output mentioned by you. Have a look at it. I'll attach the code and file itself for your reference. Thanks!

Here's the file link:
https://docs.google.com/spreadsheets/d/1GNiITngAOF9A_R8-zNfe0eEkV4pZa-3a/edit?usp=sharing&ouid=10475...

Here's the end product:

The code used:
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}
}
),
ColNames = List.Select ( Table.ColumnNames ( Source ) , each not ( try Date.From ( _ ) )[HasError] ),
Months = Source,
Group = Table.Group(Months, {"Area", "Position", "Code", "Company"}, {{"All", each Table.SelectColumns ( _ , ColNames ), type table [Area=text, Position=text, Code=text, Company=text, #"Jan-26"=nullable number, #"Feb-26"=nullable number, #"Mar-26"=number, #"Apr-26"=number, #"May-26"=number, #"Jun-26"=number, #"Jul-26"=number, #"Aug-26"=number, #"Sep-26"=number, #"Oct-26"=number, #"Nov-26"=number, #"Dec-26"=number]}}),
List = Table.TransformColumns ( Group , {"All" , each List.Combine ( Table.ToRows ( _ ) ) } ),
Cols = Table.TransformColumns ( List , {"All" , each List.Transform ( _ , each try if _ > 0.5 then List.Repeat ( {_} , _ ) else {_} otherwise {null} ) } ),
Tbl = Table.TransformColumns ( Cols ,{ "All" , each Table.FromList( _ , Splitter.SplitByNothing(), null, null, ExtraValues.Ignore ) } ),
Sep = Table.TransformColumns ( Tbl , {"All" , each Table.TransformColumns(_, { "Column1", each Text.Combine(List.Transform(_, Text.From), ", ") } ) } ),
Split = Table.TransformColumns ( Sep , {"All" , each Table.SplitColumn( _ , "Column1", Splitter.SplitTextByDelimiter( ",", QuoteStyle.Csv ), { "Column1.1", "Column1.2", "Column1.3" } ) } ),
RemoveNullCols = Table.TransformColumns ( Split , {"All" , each Table.FromRows ( List.Select ( Table.ToColumns ( _ ) , each not List.MatchesAll ( _ , each _ = null ) ) , ColNames ) } ),
ErrorCare = Table.TransformColumns ( RemoveNullCols , {"All" , each Table.TransformColumns ( _ , {} , each try if Number.From ( _ ) > 1 then 1 else Number.From ( _ ) otherwise _ ) } ),
Expand = Table.ExpandTableColumn(ErrorCare, "All", ColNames, ColNames)
in
Expand

[casar]

Thanks for your effort SundarRaj 

[BeaBF]

@casar Hi! Try with:

let
// --- Step 1: Caricamento dati ---
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("jZBBT8MwDIX/StTzQDAE91HGxKSslXoBqh1C8Lqg1YmcdNL+PXGygph64PCcL5Hz/JK2LRYEqpgVtfUmGIsRS/sJaemdwlOktcKr+UOEZ/jIIBVlWDgaT04Z1gOOcDj3DF2GBlyGSocMG3vM8ASaYTtri6bkOAPpvfIGO1HtdkYD8f2yYrtHbhBRt/9W8h2csxTYnKwG78VL78geoQcMonGgjToYzw2NXP4ZdDlsPqG7JB60xM4gAMXw8WzcMW5Wsb6+vZ8tb67vJ+tl/t8xbF/L/IQv0EFIhapL5rWUP+ZTnzCdN2XefgM=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [
#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t,
#"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t, #"(blank).13" = _t,
#"(blank).14" = _t, #"(blank).15" = _t
]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {
{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text},
{"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text},
{"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}, {"(blank).13", type text}, {"(blank).14", type text}, {"(blank).15", type text}
}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{
{"Area", type text}, {"Position", type text}, {"Code", type text}, {"Company", type text},
{"Jan-26", type number}, {"Feb-26", type number}, {"Mar-26", type number}, {"Apr-26", type number},
{"May-26", type number}, {"Jun-26", type number}, {"Jul-26", type number}, {"Aug-26", type number},
{"Sep-26", type number}, {"Oct-26", type number}, {"Nov-26", type number}, {"Dec-26", type number}
}),

// --- Step 2: Sostituisci null con 0 nei mesi ---
Mesi = {"Jan-26", "Feb-26", "Mar-26", "Apr-26", "May-26", "Jun-26", "Jul-26", "Aug-26", "Sep-26", "Oct-26", "Nov-26", "Dec-26"},
#"Replaced Nulls" = Table.ReplaceValue(#"Changed Type1", null, 0, Replacer.ReplaceValue, Mesi),

// --- Step 3: Aggiungi colonna MaxFTE (arrotondando valori < 1 a 1) ---
#"Added MaxFTE" = Table.AddColumn(#"Replaced Nulls", "MaxFTE", each
let
valori = List.Transform(Mesi, (m) => Record.Field(_, m)),
maxVal = List.Max(valori),
maxFTE = if maxVal > 0 and maxVal < 1 then 1 else Number.RoundDown(maxVal)
in
maxFTE, Int64.Type
),

// --- Step 4: Espandi righe in base a MaxFTE ---
#"Expanded Rows" = Table.ExpandListColumn(
Table.AddColumn(#"Added MaxFTE", "ExpandedRows", each List.Repeat({1}, [MaxFTE])),
"ExpandedRows"
),

// --- Step 5: Aggiungi indice di riga per gruppo per sapere quale riga è questa (da 0 a MaxFTE-1) ---
#"Grouped with Index" = Table.Group(#"Expanded Rows", {"Area", "Position", "Code", "Company"}, {
{"AllRows", each Table.AddIndexColumn(_, "IndexRow", 0, 1, Int64.Type)}
}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped with Index", "AllRows", {"Jan-26", "Feb-26", "Mar-26", "Apr-26", "May-26", "Jun-26", "Jul-26", "Aug-26", "Sep-26", "Oct-26", "Nov-26", "Dec-26", "MaxFTE", "ExpandedRows", "IndexRow"}, {"Jan-26", "Feb-26", "Mar-26", "Apr-26", "May-26", "Jun-26", "Jul-26", "Aug-26", "Sep-26", "Oct-26", "Nov-26", "Dec-26", "MaxFTE", "ExpandedRows", "IndexRow"}),
// --- Step 6: Funzione per distribuire i valori FTE in 1 o 0 per ogni riga espansa ---
DistribuisciFTE = (row as record) as record =>
let
idx = Record.Field(row, "IndexRow"),
newFields = List.Transform(Mesi, (mese) =>
let
val = Record.Field(row, mese),
valRounded = if val > 0 and val < 1 then 1 else Number.RoundDown(val),
fteValue = if idx < valRounded then 1 else 0
in
{mese, fteValue}
),
newRecord = Record.FromList(List.Transform(newFields, each _{1}), List.Transform(newFields, each _{0}))
in
Record.Combine({row, newRecord}),

// --- Step 7: Applica la funzione ad ogni riga ---
#"Distributed Months" = Table.TransformRows(#"Expanded AllRows", DistribuisciFTE),

// --- Step 8: Crea tabella da lista record ---
#"Final Table from Records" = Table.FromRecords(#"Distributed Months"),

// --- Step 9: Rimuovi colonne temporanee ---
#"Removed Columns" = Table.RemoveColumns(#"Final Table from Records", {"MaxFTE", "ExpandedRows", "IndexRow"})
in
#"Removed Columns"

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!