Forum Discussion

smjzahid's avatar
smjzahid
Icon for Helper V rankHelper V
6 years ago
Solved

List.Sum throwing error Power Query

Hi    I have created a custom column named Units (derived from an existing column in the table). I want to create another calculated column which returns me the total for each row filtered by colum...
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi smjzahid ,

     

    Please check:

     

    1. Add an Index column.

    2. Convert [Value] to list.

    3. Add a custom column.

    4. Remove Index column.

    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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    Value = #"Added Index"[Value],
    #"Added Custom Column"  = Table.AddColumn(#"Added Index", "Running Total", each if [Units] = "Hours" then List.Sum(List.Range(Value,0,[Index]+1)) else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Index"})
in
    #"Removed Columns"

     

     

    BTW, .pbix file attached.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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