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 total 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]

In the attached workbook there's your source data on the sheet Source and a Power Query query output on sheet Result.
The source data needs to be as follows:
The first 4 columns must be "Area", "Position", "Code", "Company" but those 4 can be in any order.
The rest of the columns are what the m-code tries to interpret - the column headers here can be anything (not forgetting that column headers have to be text!).
The output presents your rows and columns in the same order as your source data's rows and columns.
The code will handle values in your source data cells which have any fraction (not just 0.5) and will also handle the likes of 3.75 (which will be translated to 3 rows of 1s and 1 row of .75)



I was disappointed to find later that you've posted this question at https://community.fabric.microsoft....ues-in-several-columns-Ungrouping/m-p/4718992 where you've already had some responses. Makes me feel I may have wasted my time.

 

[p45cal]

In the attached workbook there's your source data on the sheet Source and a Power Query query output on sheet Result.

Small update to the attached workbook (Date columns in the Source table with no data are preserved in the output table).

 

[casar]

In the attached workbook there's your source data on the sheet Source and a Power Query query output on sheet Result.
The source data needs to be as follows:
The first 4 columns must be "Area", "Position", "Code", "Company" but those 4 can be in any order.
The rest of the columns are what the m-code tries to interpret - the column headers here can be anything (not forgetting that column headers have to be text!).
The output presents your rows and columns in the same order as your source data's rows and columns.
The code will handle values in your source data cells which have any fraction (not just 0.5) and will also handle the likes of 3.75 (which will be translated to 3 rows of 1s and 1 row of .75)

View attachment 90186

I was disappointed to find later that you've posted this question at https://community.fabric.microsoft....ues-in-several-columns-Ungrouping/m-p/4718992 where you've already had some responses. Makes me feel I may have wasted my time.

Hi p45cal,
Thanks for your answer! I am sorry you feel you waste your time. I just want to say, your solution is effective and elegant at the same time.
Thanks again for your super help....it's mindblowing!