List.Sum throwing error Power Query

[smjzahid]

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 column Units.

The M code for the custom column UNITS is below

= Table.AddColumn(#"Filtered Rows", "Units", each if Text.Contains([Metric], "tonnes") then "Tonnes" else if Text.Contains([Metric], "Petrol ") then "Petrol Litres" else if Text.Contains([Metric], "Hours") then "Hours" else if Text.Contains([Metric], "kWh") then "KWH" else if Text.Contains([Metric], "Tour") then "Tour" else if Text.Contains([Metric], "m3") then "Metric Cube m3" else if Text.Contains([Metric], "LPG") then "LPG Litres" else if Text.Contains([Metric], "Diesel ") then "Diesel Litres" else null)

Note: The datatype for column Value is DECIMAL NUMBER

for eg. 

if [Units] = "Hours" 
then List.Sum([VALUE])
else null

 Here is the snip of my table below 

and the error returned is below

[Icey · Accepted solution]

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.

[smjzahid]

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)

[edhans]

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]

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

[edhans]

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.

[edhans]

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.

[mahoneypat]

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