pq2023

gistfile1.txt

let
    // Load data from the '2023' sheet
    Source = Excel.CurrentWorkbook(){[Name="2023"]}[Content],

    // Skip the first 5 rows (rows 1-5)
    #"Removed Top Rows" = Table.Skip(Source, 5),

    // Promote the sixth row as headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),

    // Get all column names
    ColumnNames = Table.ColumnNames(#"Promoted Headers"),

    // Define fixed columns
    FixedColumns = {"ECODE", "Name", "Status"},

    // Find all columns containing "Total cons bonus"
    ConsBonusColumns = List.Select(ColumnNames, each Text.Contains(_, "Total cons bonus")),

    // Find all columns containing "Total man payout"
    ManPayoutColumns = List.Select(ColumnNames, each Text.Contains(_, "Total man payout")),

    // Get the latest (rightmost) "Total cons bonus" column
    LatestConsBonusColumn = if List.IsEmpty(ConsBonusColumns) then null else List.Last(ConsBonusColumns),

    // Get the latest (rightmost) "Total man payout" column
    LatestManPayoutColumn = if List.IsEmpty(ManPayoutColumns) then null else List.Last(ManPayoutColumns),

    // Combine fixed columns with the latest variable columns
    SelectedColumns = List.RemoveNulls(List.Combine({FixedColumns, {LatestConsBonusColumn, LatestManPayoutColumn}})),

    // Select the required columns from the table
    SelectedTable = Table.SelectColumns(#"Promoted Headers", SelectedColumns),

    // Change data types to number for calculation
    #"Changed Type" = if LatestConsBonusColumn <> null and LatestManPayoutColumn <> null then
        Table.TransformColumnTypes(SelectedTable, {{LatestConsBonusColumn, type number}, {LatestManPayoutColumn, type number}})
    else if LatestConsBonusColumn <> null then
        Table.TransformColumnTypes(SelectedTable, {{LatestConsBonusColumn, type number}})
    else if LatestManPayoutColumn <> null then
        Table.TransformColumnTypes(SelectedTable, {{LatestManPayoutColumn, type number}})
    else
        SelectedTable,

    // Add "To be paid" column by summing the latest bonus and payout columns
    #"Added To be paid" = Table.AddColumn(#"Changed Type", "To be paid", each
        (if LatestConsBonusColumn <> null then (if Record.Field(_, LatestConsBonusColumn) <> null then Record.Field(_, LatestConsBonusColumn) else 0) else 0) +
        (if LatestManPayoutColumn <> null then (if Record.Field(_, LatestManPayoutColumn) <> null then Record.Field(_, LatestManPayoutColumn) else 0) else 0),
        type number
    ),

    // Remove the individual bonus and payout columns
    #"Removed Columns" = if LatestConsBonusColumn <> null and LatestManPayoutColumn <> null then
        Table.RemoveColumns(#"Added To be paid", {LatestConsBonusColumn, LatestManPayoutColumn})
    else if LatestConsBonusColumn <> null then
        Table.RemoveColumns(#"Added To be paid", {LatestConsBonusColumn})
    else if LatestManPayoutColumn <> null then
        Table.RemoveColumns(#"Added To be paid", {LatestManPayoutColumn})
    else
        #"Added To be paid",

    // Reorder columns as desired
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns", {"ECODE", "Name", "To be paid", "Status"})
in
    #"Reordered Columns"