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

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.