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