Forum Discussion
Generating rows based on values in several columns - Ungrouping values in a time series dataset
I have responded at the site you cross posted at:
Hello everyone,
casar:
Another solution belowlet 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! 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!