How to sum up all the number in a table string

[Anonymous]

hello all,

I would like to ask is there any way to sum up numbers in a table string and add them into a new column.

I have this table here and inside there are three column I would like to sum up the numbers from: Usage(MB), Usage(MB)_1, Usage(MB)_2. I have been struggling because I dont know if there is a way to do it in power query

[danextian]

Hi @Anonymous 

Try this:

List.Sum([Table Column][Column A]) + List.Sum([Table Column][Column B])

[Table Column][Column A] will access a column within a table as a list. List.Sum will sum all the values in that column and will work only if the content of the column accessed are indeed numbers. If they are not numbers, the contents need to be converted to numbers first before the sum operation.

List.Sum( List.Transform( [Table Column][Column A], each try Number.From(_) otherwise null ) )

The code above tries to convert each element in the list to a number and if the conversion errors out returns null.

If a column in the table contains only one row, you can access its content with:

[Table Column][Column A]{0}
--Column A as a list then the first element of that list which is index 0

[Anonymous]

Thank you for replying me, I followed your approach and keep getting "Expression.SyntaxError: Invalid identifier" on . Did I do sth wrong?

[Table Column][Column A]
               ^^^^^^^^

[danextian · Accepted solution]

Make sure you're entering the correct columns. Power Query is case-sensitive: