Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum column range

Hi there,    I want to create a custom column in which i sum a range of columns from the dataset. It needs to be a range, as the it will be a generic report which needs to be able to handle differe...
Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    Please try below steps.

     

    1. Select [TimeStamp] column then Unpivot other columns.

    2. Add a custom column.

       

        

    3. Pivot table to convert it to original structure.

        

     

    Power Query reference:

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    Test4_Sheet = Source{[Item="Test4",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Test4_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TimeStamp", type time}, {"column A", Int64.Type}, {"column B", Int64.Type}, {"column C", Int64.Type}, {"column D", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TimeStamp"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "ColumnName"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Sum", (This) => List.Sum(Table.SelectRows(#"Renamed Columns",each [TimeStamp] = This[TimeStamp])[Value])),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[ColumnName]), "ColumnName", "Value")
in
    #"Pivoted Column"

     

    Result.

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Please try below steps.

 

1. Select [TimeStamp] column then Unpivot other columns.

2. Add a custom column.

   

    

3. Pivot table to convert it to original structure.

    

 

Power Query reference:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    Test4_Sheet = Source{[Item="Test4",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Test4_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TimeStamp", type time}, {"column A", Int64.Type}, {"column B", Int64.Type}, {"column C", Int64.Type}, {"column D", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TimeStamp"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "ColumnName"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Sum", (This) => List.Sum(Table.SelectRows(#"Renamed Columns",each [TimeStamp] = This[TimeStamp])[Value])),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[ColumnName]), "ColumnName", "Value")
in
    #"Pivoted Column"

 

Result.

 

Best regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Yuliana, 

Thank you thatshould work the way i want it too. Little question though, in your code you use "(this)", can you explain what that does?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I also found a way to do it with R-script without any errors:

     

    let
    Source = Source,
    #"Removed Other Columns" = Table.SelectColumns(Source, ListColumns),
    #"Run R Script" = R.Execute("dataset$Total <- as.integer(rowSums(dataset[2:"&Parameter&"], na.rm = TRUE))#(lf)temp <- as.vector(c(""Timestamp"", ""Total""))#(lf)dataset <- dataset[, temp]#(lf)output <- dataset",[dataset=#"Removed Other Columns"]),
    #"""output""" = #"Run R Script"{[Name="output"]}[Value]
in
    #"""output"""

    I use a parameter to handle the dynamic part of the report. Meaning that whenever the user connects to the report he/she will first fill in how many products there are present in the dataset. Using the parameter only works when i fill it in through the advanced editor, not in the r-script window. 

     

    I first load the source query, filter out the products that i don't need (using a list will ensure it will always work, independent from the amount or names of the product). Then i create a new column (Total) which holds the sum of all columns between column 2 and the parameter. 

     

    Depending on the goal of the file both solutions seem to work, i did not test speed or performance impact. 

     

    I hope this will also help other people who encounter simulair problems :)