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

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=104752674875039603034&rtpof=true&sd=true

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's avatar
casar
Regular Visitor
1 year ago

Thanks for your effort SundarRaj