POWER QUERY - HOW TO: SUM all columns EXCEPT A, B & C

[losamfr17]

Good morning,

My dilemma:
I have a spreadsheet that roughly looks like the one below. IT refreshes that file every week with an additional week of data.
[TABLE="width: 500"]
<tbody>[TR]
[TD]DoorID[/TD]
[TD]CustName[/TD]
[TD]January Week 1[/TD]
[TD]January Week 2[/TD]
[TD]January Week 4[/TD]
[TD]February Week 1[/TD]
[TD]February Week 2[/TD]
[TD].....[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Macy's[/TD]
[TD]10[/TD]
[TD]13[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD].....[/TD]
[/TR]
[TR]
[TD]4578[/TD]
[TD]Saks[/TD]
[TD]23[/TD]
[TD]43[/TD]
[TD]11[/TD]
[TD]72[/TD]
[TD]7[/TD]
[TD].....[/TD]
[/TR]
</tbody>[/TABLE]

My goal:
I want my Power Query model to SUM all columns EXCEPT for DoorID and CustName (I really have a couple more columns to exclude), so that as the weeks keep piling on, I can refresh the query to include the last weeks that were just inserted by IT. Is there a way to do this?

I am not unpivoting the columns because otherwise the data range gets way to big - I mean over 20MM rows. It takes too long to refresh.

Thank you!

 

[ParamRay]

Split into two queries...

1st query unpivots the data, and then does a sum operation, grouping by DoorID. (So you get one row per DoorID with a sum of all the weeks.)
2nd query merges the 1st query with original data in your post, using DoorID to match the rows.

 

[Ron Coderre]

Maybe this? ...
Using your sample data, I...
• Named the Data Range: rngData
• Created a Power Query reference to it
• Created a second query that references the rngData query
• Set the first column to include in the sum
• set the number of columns to sum
• Created a row total
These are the steps:

let
    Source = rngData,
    FirstColRef = 2,
    NumColsToSum = Table.ColumnCount(rngData)-FirstColRef,
    RowTotal = Table.AddColumn(rngData, "Sum", each List.Sum(List.Range(Record.ToList(_),FirstColRef,NumColsToSum)))
in
    RowTotal

These are the results:

DoorID   CustName  January Week 1   January Week 2   January Week 4   February Week 1 February Week 2 Sum
12345    Macy's    10               13               20                2               9                54
 4578    Saks      23               43               11               72               7               156

Is that something you can work with?

 

[losamfr17]

Hi,

Sorry for my late follow up.
Do you insert your code as an 'Insert Step After'? Where does the code reference the file path?
I'm getting an error using the modified code below:

let    Source = rngData,  
    rngData = Excel.Workbook(File.Contents("C:\Users\mickael.taieb\Desktop\Copy of Clarins.Sell Thru by week by door 18.02.12.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DOORID", type text}, {"TOP DOOR CODE", type text}, {"ASDNAME", type text}, {"TRAINER #", Int64.Type}, {"TRAINER NAME", type text}, {"EduSpecialist", type text}, {"ASD", Int64.Type}, {"AENAME", type text}, {"AE", Int64.Type}, {"CHAIN NAME", type text}, {"CUSTOMER NAME", type text}, {"ADDRESS LINE 1", type text}, {"CITY", type text}, {"ST", type text}, {"ZIP CD", Int64.Type}, {"Cust. NO.", Int64.Type}, {"Cust. NO. 2", Int64.Type}, {"January 2018 Week 1 Totals", type number}, {"January 2018 Week 2 Totals", type number}, {"January 2018 Week 3 Totals", type number}, {"January 2018 Week 4 Totals", type number}, {"January 2018 Week 5 Totals", type number}, {"February 2018 Week 1 Totals", type number}}),
    FirstColRef = 6,
    NumColsToSum = Table.ColumnCount(Source)-FirstColRef,
    RowTotal = Table.AddColumn(Source, "Sum", each List.Sum(List.Range(Record.ToList(_),FirstColRef,NumColsToSum)))
in
    RowTotal

 

[Ron Coderre]

You didn't mention that you needed to import the data, so I assumed you already had it in your workbook.
That's why my first comment was to add the data range to Power Query.

So, if you create a query to your source data and name that query rngData, then my posted code will likely work.
Let us know how you fare.