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.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

I had an error in my first formula but edited it within 10min. Can you verify? Post your formula here if you are still getting the wrong value.

smjzahid's avatar
smjzahid
Icon for Helper V rankHelper V
6 years ago
= Table.AddColumn(#"Changed Type", "Total Hours", each if [Units] = "Hours" 
then List.Sum(
    Table.SelectRows(
        #"Changed Type", each [Units] = "Hours")
        [VALUE])
        
else null)
  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi smjzahid , is this what you are wanting, what is in the far right "custom" column?

    If so, your formula should just be:

     

     

    if [Units] = "Hours" then [Value] else null

     

     

    No need for List.Sum. Sorry if I've overcomplicated it. I used List.Sum to total all rows where Units = "Hours" which is why it is 10 for those for rows (1+2+3+4) and null for the kWh rows.

     

    If that still isn't what you need, please give us an example of what you expect. You can screenshot somethig from Excel to mock it up if you want, just explain the logic.

     

    If this is what you want, then Power Query is the best place to do this. Power Query excels in this kind of transformation that is done for each record vs trying to access an entire table at a time per record.