Calculate sum of column in query editor

[BryanSt]

Hi There

My source data is updated quarterly and has columns included for the rest of the Financial Year, which are all zero for future periods, until there are actuals for that quarter.

I would like to perform a sum on the applicable future columns to see if it has any values in it other than zero. If not zero then I do some movement calculations else I take the zero

I have tried using a List.Sum on the applciable columns (List.Sum([ActualDecSum]) as part of the If statement, but that really slows down the query as it looks to me that the query is evaluating the source data multiple times.  That is my source is approx 7MB as the base file.  When the query in running it reaches ~30MB and then starts again.  See picture.  I do these List.Sums on 3 columns of approx 20.

The query does work, but is really slow.  Without that step it loads in less than 15 seconds, but with it it is several minutes.

I have tried (with very limited knowledge) to use the Statistics (from the Number Column section of the Ribbon under Transform )in Power Query, which gives the Sum I am after, but then stops the rest of the query.  

Any help gratefully accpeted.

Thanks

[ImkeF]

Key is to let these calculations run on partitions of the table only instead of the full table. Use Table.Group to split the large table up into smaller partitions according to your if-statements.

If you need help with it, please paste the full code of your aggregate/if-statement.

[BryanSt]

Imke

Thanks for the prompt reply.

Here is the code from Advanced Editor.  I have included all the future quarters (Dec, Mar and Jun), but only shown the If statement for December for simplicity.  The code for March and June follow the same logic.

ActSep0 = Table.AddColumn(ActJun1, "Act_Sep_0", each [ActualSepCurrent_Sum]),
SumDec0 = Table.AddColumn(ActSep0, "SumofDec0", each List.Sum(#"Changed Type"[ActualDecCurrent_Sum])),
SumMar0 = Table.AddColumn(SumDec0, "SumofMar0", each List.Sum(#"Changed Type"[ActualMarCurrent_Sum])),
SumJun0 = Table.AddColumn(SumMar0, "SumofJun0", each List.Sum(#"Changed Type"[ActualJunCurrent_Sum])),
#"Changed Sum Columns" = Table.TransformColumnTypes(SumJun0,{{"SumofDec0", Int64.Type}, {"SumofMar0", Int64.Type},{"SumofJun0", Int64.Type}}),

ActDec0 = Table.AddColumn(#"Changed Sum Columns", "Act_Dec_0", each if [SumofDec0] <> 0
then
if([Acct_Code]>= 400000000 and [Acct_Code]< 900000000)
then
[ActualDecCurrent_Sum]-[ActualSepCurrent_Sum]
else
[ActualDecCurrent_Sum]
else
[ActualDecCurrent_Sum]),

[ImkeF]

That looks really bad: So your (quarterly) amounts come in in different columns already?

Then you should probably start with an unpivot.

But I would actually need to see a sample of your table to give a good advice here.

[BryanSt]

Imke

Yes the data comes in as columns, but as cumulated values (that is each quater only shows the YTD value) so I need to calculate the movement to get each quarter's value ("the movement calcs") depending on the Acct Code.  The Acct_Code related to Income Statement, Balance Sheet and Cash Flow, so I need to treat them differently.

I do unpivot it later once I have done the movement calcs.  I have about 6 years of quarterly data that I have to calculate the movements.  so it was easier to do that in columns than rows.

Unfortunalety I can't attach the data file as it is sensitive.  

Is there a way of adding some additional filter to the existing List.Sum so that it only does the one colum?  

[ImkeF]

I didn't come across a good reason yet to proceed as you do (and don't unpivot sooner), but of course I cannot say for sure without seeing the full picture.

Power Query is a lazy language and it will create an execution plan once it processes the full query code. Thereby it will try to cache certain things (but unfortunately it doesn't tell us what and why...).

That being said, it COULD be that you List.Sums are not being chached and instead will be re-evaluated for every row (although they will return the same value in each row).

You can circumvent this if you calculate them by definition only once like this:

SumDec0 = List.Sum(#"Changed Type"[ActualDecCurrent_Sum]),

You will then reference by using the step/variable-name (so without square brackets) like so:

ActDec0 = Table.AddColumn(#"Changed Sum Columns", "Act_Dec_0", each if SumDec0 <> 0
then
if([Acct_Code]>= 400000000 and [Acct_Code]< 900000000)
then
[ActualDecCurrent_Sum]-[ActualSepCurrent_Sum]
else
[ActualDecCurrent_Sum]
else
[ActualDecCurrent_Sum]),

 If you reference SumDec0 multiple times, buffering it could also speed up the process:

SumDec0 = List.Buffer(List.Sum(#"Changed Type"[ActualDecCurrent_Sum]) ),

[BryanSt]

Imke

Thanks very much for the help so far.

I have implemented what you suggested above and corrected a rookie error in the Previous Step name (fool me for copying some other code from the Community).

I am now getting the following error in my SumDec0 step.  The ActualDecCurrent_Sum is a whole number type

Expression.Error: We cannot convert the value 3760077295 to type List.
Details:
Value=3760077295
Type=Type

Thanks again

[ImkeF]

Hi @BryanSt,

the error-message says that you've integrated the code incorrectly (you reference a field that returns a single field instead of a list). To help you further, you have to share your code.

[BryanSt · Accepted solution]

Imke

Had to do a work around that has resolved the road block I was hitting.  It might not be efficient, but it getst the job done.

I am loaded the main data file in several times so that I can use the Statistics to get a total for each of the current year years.  That is I load the file, delete all other columns and then do a use the Statistics option from the menu and select Sum and then convert to a List.  I do that for 3 quarters so I have three lists with one value in each.

I have removed some of the nonessential code to show the new List query.

let
    Source = Excel.Workbook(File.Contents("xxxxx.xlsx"), null, true),
    QAFP_Data_DefinedName = Source{[Item="QAFP_Data",Kind="DefinedName"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(QAFP_Data_DefinedName, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{yyy}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type"All columns except applicable quater}),
    #"Calculated Sum" = List.Sum(#"Removed Columns"[ActualDecCurrent_Sum]),
    #"Converted to List" = {#"Calculated Sum"}
in
    #"Converted to List"

I then refer to those Lists in orginal code.

I dont get an error and the data is not looping as it was (when it was gettin getting to over 30MB).

[BryanSt]

Imke

Yes the data comes in as columns, but as cumulated values (that is each quater only shows the YTD value) so I need to calculate the movement to get each quarter's value ("the movement calcs") depending on the Acct Code.  The Acct_Code related to Income Statement, Balance Sheet and Cash Flow, so I need to treat them differently.

I do unpivot it later once I have done the movement calcs.  I have about 6 years of quarterly data that I have to calculate the movements.  so it was easier to do that in columns than rows.

Unfortunalety I can't attach the data file as it is sensitive.  

Is there a way of adding some additional filter to the existing List.Sum so that it only does the one colum?