Summing Columns Based on Column Header String

[Mars3442]

Hi everyone,

I want to ask a help for solving my problem

I have a table of Raw Data like this

I need to sum the value per row when the header contains "." and "Qty" It will create a new column "TOTAL"

After that, I want to calculate the GAP = QTY PO CS - TOTAL

My expectation table

What M Code should I write to get my expectations?

Please help me to solve this problem

Thank you..

[j_ocean]

If the columns are fixed then just highlight the columns you want to sum and click add column > sum. Then you can click the two other columns and add column > subtract.

If your columns are *not* fixed then your table is not organized properly.

[AlienSx]

Hello, @Mars3442 

let
    Source = your_table,
    dots = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.Contains(_, "."))),
    total = Table.AddColumn(
        Source, "TOTAL", 
        each List.Sum(
            Record.FieldValues(
                Record.SelectFields(_, dots)
            )
        )
    ),
    gap = Table.AddColumn(total, "GAP", each [QTY PO CS] - [TOTAL])
in
    gap

[Mars3442]

Hi thank you for your solution but I get an error

I modify your solution like this

List = List.Buffer(List.Select(Table.ColumnNames(#"Previous Step"), each Text.Contains(_, ".")and Text.Contains(_, "Qty CS"))),
Total = Table.AddColumn( List, "Total Reason QTY CS", each List.Sum(Record.FieldValues(Record.SelectFields(_, List))))

but I got an error

Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]

what should I do?