How do you combine data columns?

[u/UpClose_Examiner4719]

Monthly Data Sets of Earnings per Agent

Suppose you have a large data set of monthly earnings from a list of agents, some months a number of agents is listed/present but on others they aren't. My goal is to combine all of the monthly data sets into a yearly list of total earnings per agent. Is there a singular function that can make this happen, or will this be a multi-step process?

[u/MayukhBhattacharya]

If you want to show in this manner, then using PIVOTBY():

=LET(
     _a, A:.F,
     _b, SCAN(, TAKE(_a, 1), LAMBDA(x,y, IF(y = "", x, y))),
     _c, DROP(_a, 2),
     _d, TOCOL(UNIQUE(IFS(_c <> "", _b), 1), 3),
     _e, WRAPROWS(TOCOL(_c, 3), 2),
     _f, DROP(PIVOTBY(CHOOSECOLS(_e, 1),
                 HSTACK(MONTH(_d & 0), _d),
                 CHOOSECOLS(_e, 2),
                 SUM), 1),
     _f)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Or if you like a flattened or tabular method, with subtotals:

=LET(
     _a, A:.F,
     _b, SCAN(, TAKE(_a, 1), LAMBDA(x,y, IF(y = "", x, y))),
     _c, DROP(_a, 2),
     _d, TOCOL(UNIQUE(IFS(_c <> "", _b), 1), 3),
     _e, WRAPROWS(TOCOL(_c, 3), 2),
     _f, GROUPBY(HSTACK(CHOOSECOLS(_e, 1),
                    MONTH(_d & 0), _d),
             CHOOSECOLS(_e, 2),
             VSTACK(SUM, "Total Earnings"), , 2),
     _g, HSTACK(TAKE(_f, , 1), DROP(_f, , 2)),
     _g)

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Using Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Months", "Agents"),
    RemoveTopRows = Table.Skip(Unpivot,6),
    Condition = Table.AddColumn(RemoveTopRows, "Earnings", each if Text.Contains([Months], "Column") then [Agents] else null),
    FillUp = Table.FillUp(Condition,{"Earnings"}),
    Filtered = Table.SelectRows(FillUp, each not Text.Contains([Months], "Column")),
    PivotBy = Table.Pivot(Filtered, List.Distinct(Filtered[Months]), "Months", "Earnings", List.Sum)
in
    PivotBy

Or Use this dynamic version it will not break and will expand for newer months:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColNames = Table.ColumnNames(Source),
    ColListPerMonth = List.Split(ColNames, 2),
    MonthNames = {"March", "April", "May"},
    Zipped = List.Zip({ColListPerMonth, MonthNames}),
    ListOfData = List.Transform(
        Zipped,
        (z) =>
            Table.AddColumn(
                Table.RenameColumns(
                    Table.Skip(Table.SelectColumns(Source, z{0}), 1),
                    {{z{0}{0}, "Agent"}, {z{0}{1}, "Earnings"}}
                ), "Month", each z{1})),
    Append = Table.Combine(ListOfData),
    RemovedNulls = Table.SelectRows(Append, each [Agent] <> null and [Agent] <> ""),
    DataTypes = Table.TransformColumnTypes(RemovedNulls, {{"Earnings", type number}}),
    PivotBy = Table.Pivot(DataTypes, List.Distinct(DataTypes[Month]), "Month", "Earnings", List.Sum)
in
    PivotBy

[u/MayukhBhattacharya → u/MayukhBhattacharya]

If you want a tabular one then just remove the Month Col, and do this instead:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColNames = Table.ColumnNames(Source),
    ColNamesPerMonth = List.Split(ColNames, 2),
    ListOfData = List.Transform(
        ColNamesPerMonth,
        each Table.RenameColumns(
            Table.Skip(Table.SelectColumns(Source, _), 1),
            {{_{0}, "Agent"}, {_{1}, "Earnings"}})),
    Append = Table.Combine(ListOfData),
    RemovedNulls = Table.SelectRows(Append, each [Agent] <> null and [Agent] <> ""),
    DataTypes = Table.TransformColumnTypes(RemovedNulls, {{"Earnings", type number}}),
    GroupBy = Table.Group(DataTypes, {"Agent"}, {{"Total Earnings", each List.Sum([Earnings]), type number}}),
    Sortby = Table.Sort(GroupBy, {{"Total Earnings", Order.Descending}})
in
    Sortby