Forum Discussion
Build sum per line, via different columns with changing header
Dear Community,
hope s.b. can support me with an idea to solve my issue.
I got data from standard system report, that provide 10 columns - unfortunately with moving forward in time, the output of report will lead to a change of the headline of these 10 columns.
e.g.
today it says column1 = IN - Thursday; column2 = IN - Friday
tomorrow it will say column1 = IN - Friday; colum2 = IN - Monday
i want to calculate the total of these 10 columns with the changing headlines, but i'm stuck - i know/found out there is a function calls "Table.SelectColumn" but i have no idea if this one might bring me solution as i'm bit new with PB.
thanks in advance for any help/idea.
BR Mic
Just use this formula in a custom column. This formula skips first 3 columns. If you want to skip any other columns in the beginning, replace 3 accordingly.
List.Sum(List.Skip(Record.ToList(_), 3))If no column to be skipped
List.Sum(Record.ToList(_))There are various ways to handle any other requirement like say you want to capture column 3 to column 12 (then List.Range will have to be used). Say you want to sum only those columns which start with IN -
Let us know.
5 Replies
Just use this formula in a custom column. This formula skips first 3 columns. If you want to skip any other columns in the beginning, replace 3 accordingly.
List.Sum(List.Skip(Record.ToList(_), 3))If no column to be skipped
List.Sum(Record.ToList(_))There are various ways to handle any other requirement like say you want to capture column 3 to column 12 (then List.Range will have to be used). Say you want to sum only those columns which start with IN -
Let us know.
Hello Vijay_A_Verma,
thx for your hint - tried to rebuilt this, unfortunately it returns a "Error", eventhough all column have format integer.
Error shows "The value for a Precision.Type Enumeration was not one of the allowed values: "Precision.Double, Precision.Decimal"
As far as i got this message (english is not my mother lang) i probably have issues with different format of numbers.
Do you have a hint on that?
What is the final formula used by you?
finally i used the one to skip the inital 14 colums
=List.Sum(List.Skip(Record.ToList(_)),14)
as first with column 15 those colums are in the total file i want to built the sum from
ahh,.. sorry. i put a ")" on the wrong spot... now it works - my fault
