none
Dynamically sum columns RRS feed

  • Question

  • Hi all,

    I'm a new bee too Power BI and I hope you can help me with the following challenge. I've already tried several options but am really stuck.

    I have the following source:

    YearMonth | 201406 | 201407
    
    201501     |          1  |           0
    
    201502     |          1  |           1


    I would like to group by YearMonth and Sum the other 2 columns. I've created a function which I call with 

    Table.Group(#"Columns deleted", {"YearMonth"}, fMyFunction(#"Columns deleted"))

    let fMyFunction = (myinput as table) as list=> let GetYearMonthColumns = List.RemoveMatchingItems(Table.ColumnNames(myinput), {"YearMonth"}), TransformedList = List.Transform(GetYearMonthColumns, each { _ , each List.Sum(Table.Column( myinput ,_)) , type number }), Source = TransformedList in Source in fMyFunction


    Unfortunately this results in an error :( Can somebody point me in the right direction?

    Best Regards,

    Roeland


    • Edited by SalijR Friday, September 18, 2015 4:21 PM
    Friday, September 18, 2015 4:20 PM

Answers

  • Try something like this below

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"YearMonth"}, "Atrybut", "Wartość"),
        Group = Table.Group(Unpivot, {"YearMonth", "Atrybut"}, {{"sum", each List.Sum([Wartość]), type number}}),
        Pivot = Table.Pivot(Group, List.Distinct(Group[Atrybut]), "Atrybut", "sum")
    in
        Pivot


    • Edited by Bill Szysz Friday, September 18, 2015 6:59 PM
    • Marked as answer by SalijR Friday, September 18, 2015 7:49 PM
    Friday, September 18, 2015 6:58 PM

All replies

  • Well, if you don't have to do this in Power Query, just import the table you have and:

    1. Create a new calculated column with the formula "=[201406]+[201407]"

    2. Create a table visualization that displays YearMonth and this new calculated field.

    Friday, September 18, 2015 4:33 PM
  • Thanks for your response!

    I'm open for ideas other than Power Query, as long as it's Power BI Desktop :).

    To make myself clear, I would like to sum the rows each column individually,  grouping the rows by YearMonth.

    YearMonth | 201406 | 201407
    
    201501     |          1  |           0
    
    201502     |          1  |           1
    
    201501     |          1  |           1


    The result would become

    YearMonth | 201406 | 201407
    
    201501     |          2  |           1
    
    201502     |          1  |           1
    
    

    Friday, September 18, 2015 4:42 PM
  • Oh, for that you want a calculated field of the form:

    SUM1:=SUM([201406])

    SUM2:=SUM([201407])

    Then, create a matrix visualization with YearMonth, SUM1 and SUM2, grouping in your matrix on YearMonth (row). The matrix will automagically filter the SUM1 and SUM2 fields based upon the row they are in.

    Friday, September 18, 2015 4:46 PM
  • Power Query allows you to perform 'Group by' operations and specify the aggregation method.

    Select your YearMonth column, then right-click and select the "Group by" menu item.

    Give the same names to the new columns, select the aggregation operation, chose the columns ... it's all quite straightforward.

    Based on your example, here is the code that is generated:

     
    let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    #"Grouped Rows" = Table.Group(Source, {"YearMonth "}, {{"201406", each List.Sum([201406]), type number}, {"201407", each List.Sum([201407]), type number}})in    #"Grouped Rows"

    Screenshot:

    Friday, September 18, 2015 5:32 PM
  • Thats exactly the result i would like to achieve ideally, dynamically and programmatically, in M, as there are 24 columns like 201406( increments every month and added dynamically with m from a previous source table) Currently the code breaks on the table.column(..,...) part. If I replace it with [201406] it doesn't break. It results in the sum of rows in 201406 column for every column. Thanks for your response
    Friday, September 18, 2015 6:22 PM
  • Try something like this below

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"YearMonth"}, "Atrybut", "Wartość"),
        Group = Table.Group(Unpivot, {"YearMonth", "Atrybut"}, {{"sum", each List.Sum([Wartość]), type number}}),
        Pivot = Table.Pivot(Group, List.Distinct(Group[Atrybut]), "Atrybut", "sum")
    in
        Pivot


    • Edited by Bill Szysz Friday, September 18, 2015 6:59 PM
    • Marked as answer by SalijR Friday, September 18, 2015 7:49 PM
    Friday, September 18, 2015 6:58 PM
  • Brilliant! Thank you so much!
    Friday, September 18, 2015 7:49 PM