Make costum a calculated column with formula (last column - sum "Volume Third Party" : last column)

[Anonymous]

Hi! I have a power query which gets data from a table. These table containts specific columns which are called "Volume Third Party". Table is dynamic which means that the number of columns including columns which are called "Volume Third Party"  can increase or decrease. I want to add costum column with the following formula (last column - sum range between "Volume Third Party" to last column). Between these range there are columns with the name "Volume Third Party_*different number*". I have attached screenshot to make it more clear:

After adding costum column it should add filter to last column and to column with custom function which would remove rows with 0 & blank values. Is it possible to do? 

Current code:

let
    Source = Excel.CurrentWorkbook(){[Name="SAPCrosstab1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([#""] <> "R001" and [#""] <> "R003") and ([_6] <> "#"))
in
    #"Filtered Rows"

[ronrsnfld · Accepted solution]

Without an example of your data and results, it's hard to tell exactly what you want, but this custom column formula should get you started.

   [Last Column] -
        List.Sum(
            Record.FieldValues(
                Record.SelectFields(_,
                    List.Select(
                        List.RemoveLastN(Record.FieldNames(_),1), 
                        each Text.StartsWith(_,"Volume Third Party")))))

In the formula above, know that:

• The underscore "_" argument in Record.SelectFields will return the entire row as a record
• #"Changed Type" is the previous step.  Alter that if necessary
• List.RemoveLastN(... removes the Last column name from the list of Field names
• List.Select returns a list of all the remaining Field names that start with "Volume Third Part"
• Record.Select returns just those fields from the entire record (row)
• And finally we Sum the values of those particular records to subtract from the value in the Last Column

Adding a filter, or changing which columns to sum/subtract, should be easy for you to modify.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.