List.Sum throwing error Power Query

Why not do this in a DAX calculated column?  It would be easier.  However, your expression is trying to use the value in the current row of the [Value] column, which is why it can't convert it to a list.  To do what you are looking for, you will need something like this to filter the table from the previous step to just the rows for "Hours" and reference the [Value] column to get the list of values.

= List.Sum(Table.SelectRows(#"Previous Step", each [Units]="Hours")[Value])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

You have to reference the entire table, then specify the column, and the table is the previous step, which needs to be filtered.
The key formula is:

if [Units] = "Hours" then 
     List.Sum(
         Table.SelectRows(
             #"Changed Type",
             each [Units] = "Hours"
         )[Value]
     ) 
else null

To see a full example of this use this M code below by pasting it into a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgvLSpW0lEyVIrVQfCMUHjGKDwTMC87PAOuC86OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Units = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}}),
    #"Add Total" = 
        Table.AddColumn(
            #"Changed Type",
            "Total",
            each
                if [Units] = "Hours" then 
                    List.Sum(
                        Table.SelectRows(
                            #"Changed Type",
                            each
                            [Units] = "Hours"
                        )[Value]) 
                else null
                        
        )
in
    #"Add Total"

This will return the following new column called Total:

Note that this will not perform well on large data sets. Your better bet is to return the data to DAX and use a measure to return that information. You can use a calculated column, but those have issues of their own.

But if you need it in Power Query, and your dataset isn't too large, this will work fine.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Hi edhans

Your formual works, However it gives me a Grand Total summed up in all rows (the value is same for all row. 

I am expecting it to give me total row by row (similar to how SUMX and other iterator function works in DAX ).

Below is what I am getting is shown below.

I want the result like column Value (see the red arrow)