none
running sum RRS feed

  • Question

  • Hi,

    I have the following table

    Year	Month	Value
    2011	2	10
    2011	2	5
    2012	5	20
    2014	8	3
    2011	7	20
    2012	6	10
    2014	12	5

    I like to get this table with power query

    Year	Month	runningSumValue
    2011	2	15
    2011	7	35
    2012	5	55
    2012	6	65
    2014	8	68
    2014	12	73

    Thanks a lot!


    • Edited by Michael8412 Sunday, December 30, 2018 9:32 AM
    Sunday, December 30, 2018 9:30 AM

Answers

  • But it seems for me as a comparable problem. If apples & oranges were comparable I would agree ;-)

    Hey Michael

    Could you check the following

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Year Month", Int64.Type}, {"Value", Int64.Type}, {"Valid until", type date}}),
        #"Duplicated YearMonth" = Table.DuplicateColumn(#"Changed Type", "Year Month", "YearMonth.1"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated YearMonth",{{"YearMonth.1", type text}}),
        #"Added Period" = Table.AddColumn(#"Changed Type1", "Period", each
            if Text.Length([#"YearMonth.1"]) = 5
            then #date(Number.From(Text.Start([#"YearMonth.1"],4)), Number.From("0"&Text.Range([#"YearMonth.1"],4)),1)
            else #date(Number.From(Text.Start([#"YearMonth.1"],4)), Number.From(Text.Range([#"YearMonth.1"],4)),1),
            type date),
        #"Removed Columns" = Table.SelectColumns(#"Added Period",{"Year Month", "Value", "Valid until", "Period"}),
        #"Grouped Rows" = Table.Group(#"Removed Columns", {"Period", "Year Month", "Valid until"},
            {{"TotalPeriod", each List.Sum([Value]), type number}}),
        #"Sorted Period" = Table.Sort(#"Grouped Rows",{{"Period", Order.Ascending}}),
        #"Added Index1" = Table.AddIndexColumn(#"Sorted Period", "Index1", 1, 1),
        #"Added Index0" = Table.AddIndexColumn(#"Added Index1", "Index0", 0, 1),
        #"Merged On Indexes" = Table.NestedJoin(#"Added Index0",{"Index0"},
                                                #"Added Index0",{"Index1"},
                                                "Merge", JoinKind.LeftOuter),
        #"Expanded Data" = Table.ExpandTableColumn(#"Merged On Indexes", "Merge",
            {"Valid until", "TotalPeriod"}, {"ValidUntil.1", "TotalPeriod.1"}),
        #"Removed Columns1" = Table.SelectColumns(#"Expanded Data",
            {"Year Month", "Valid until", "TotalPeriod", "Index1", "ValidUntil.1", "TotalPeriod.1"}),
        #"Added ToSubstract" = Table.AddColumn(#"Removed Columns1", "ToSubstract", each
            if [ValidUntil.1] = null then 0 else [TotalPeriod.1],
            type number),
        #"Removed Columns2" = Table.SelectColumns(#"Added ToSubstract",
            {"Year Month", "Valid until", "TotalPeriod", "Index1", "ToSubstract"}),
        #"Added Custom RunTotal" = Table.AddColumn(#"Removed Columns2", "Custom Run Total", each
            List.Sum(List.FirstN(#"Removed Columns2"[TotalPeriod],[Index1]))
            - List.Sum(List.FirstN(#"Removed Columns2"[ToSubstract],[Index1])), type number),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom RunTotal",
            {"Year Month", "TotalPeriod", "Valid until", "Custom Run Total"})
    in
        #"Removed Other Columns"

    Wednesday, January 2, 2019 6:46 AM

All replies

  • Hi Michael

    ...
        #"Grouped Rows" = Table.Group(PreviousStepName, {"Year Month"}, {{"TotalPeriod",
            each List.Sum([Value]), type number}}),
        #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Year Month", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Run. Total",
            each List.Sum(List.Range(#"Added Index"[TotalPeriod], 0, [Index])), type number),
    ...

    Alternative for last step

        #"Added Custom" = Table.AddColumn(#"Added Index", "Run. Total",
            each List.Sum(List.FirstN(#"Added Index"[TotalPeriod], [Index])), type number),
    


    EDIT: Found out thread Runnig total in power Query. Worthwhile reading...

    • Edited by Lz._ Sunday, December 30, 2018 1:17 PM Additional info
    • Proposed as answer by Lz._ Tuesday, January 8, 2019 4:19 PM
    Sunday, December 30, 2018 12:10 PM
  • Thank you for your solution. It does work.

    I need to do a final step: The table looks now like this

    Year	Month	Value	Valid until
    2011	2	10	
    2011	2	5	
    2012	5	20	31.05.2012
    2014	8	3	
    2011	7	20	
    2012	6	10	
    2014	12	5	

    I like to get this table now.

    Year	Month	runningSumValue	
    2011	2	15	
    2011	7	35	
    2012	5	55	
    2012	6	35	
    2014	8	48	
    2014	12	53	

    Might be complicated?


    The Link you have posted is very hard stuff for me. I need an easy example first.
    • Edited by Michael8412 Sunday, December 30, 2018 2:00 PM
    Sunday, December 30, 2018 1:59 PM
  • Hi Michael

    Totally different problem - next time open a separate thread please

    In your expected output I can't reconcile the 35 for 20126. As I understand what you intend to do this should be 45, correct? If not please explain

    Tuesday, January 1, 2019 4:10 PM
  • Hi Lz._

    of course you are right. I expect 45 as value for 20126. And sorry I didn't open a new thread. But it seems for me as a comparable problem.

    Tuesday, January 1, 2019 6:06 PM
  • But it seems for me as a comparable problem. If apples & oranges were comparable I would agree ;-)

    Hey Michael

    Could you check the following

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Year Month", Int64.Type}, {"Value", Int64.Type}, {"Valid until", type date}}),
        #"Duplicated YearMonth" = Table.DuplicateColumn(#"Changed Type", "Year Month", "YearMonth.1"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated YearMonth",{{"YearMonth.1", type text}}),
        #"Added Period" = Table.AddColumn(#"Changed Type1", "Period", each
            if Text.Length([#"YearMonth.1"]) = 5
            then #date(Number.From(Text.Start([#"YearMonth.1"],4)), Number.From("0"&Text.Range([#"YearMonth.1"],4)),1)
            else #date(Number.From(Text.Start([#"YearMonth.1"],4)), Number.From(Text.Range([#"YearMonth.1"],4)),1),
            type date),
        #"Removed Columns" = Table.SelectColumns(#"Added Period",{"Year Month", "Value", "Valid until", "Period"}),
        #"Grouped Rows" = Table.Group(#"Removed Columns", {"Period", "Year Month", "Valid until"},
            {{"TotalPeriod", each List.Sum([Value]), type number}}),
        #"Sorted Period" = Table.Sort(#"Grouped Rows",{{"Period", Order.Ascending}}),
        #"Added Index1" = Table.AddIndexColumn(#"Sorted Period", "Index1", 1, 1),
        #"Added Index0" = Table.AddIndexColumn(#"Added Index1", "Index0", 0, 1),
        #"Merged On Indexes" = Table.NestedJoin(#"Added Index0",{"Index0"},
                                                #"Added Index0",{"Index1"},
                                                "Merge", JoinKind.LeftOuter),
        #"Expanded Data" = Table.ExpandTableColumn(#"Merged On Indexes", "Merge",
            {"Valid until", "TotalPeriod"}, {"ValidUntil.1", "TotalPeriod.1"}),
        #"Removed Columns1" = Table.SelectColumns(#"Expanded Data",
            {"Year Month", "Valid until", "TotalPeriod", "Index1", "ValidUntil.1", "TotalPeriod.1"}),
        #"Added ToSubstract" = Table.AddColumn(#"Removed Columns1", "ToSubstract", each
            if [ValidUntil.1] = null then 0 else [TotalPeriod.1],
            type number),
        #"Removed Columns2" = Table.SelectColumns(#"Added ToSubstract",
            {"Year Month", "Valid until", "TotalPeriod", "Index1", "ToSubstract"}),
        #"Added Custom RunTotal" = Table.AddColumn(#"Removed Columns2", "Custom Run Total", each
            List.Sum(List.FirstN(#"Removed Columns2"[TotalPeriod],[Index1]))
            - List.Sum(List.FirstN(#"Removed Columns2"[ToSubstract],[Index1])), type number),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom RunTotal",
            {"Year Month", "TotalPeriod", "Valid until", "Custom Run Total"})
    in
        #"Removed Other Columns"

    Wednesday, January 2, 2019 6:46 AM
  • Hey Lz._

    I have tested it now and it works very well. Thank you for spending your time.

    Do you know what I can do if the table looks like this

    Date	      Value     Colour	Weather	  Valid until
    04.12.2013	10	red	sunny	
    08.01.2011	5	green	rain	
    20.08.2012	20	blue	rain	   31.05.2014
    28.01.2011	10	red	sunny	
    06.04.2013	20	blue	sunny	
    10.05.2014	10	green	sunny	
    20.09.2015	20	blue	rain	
    24.02.2011	5	blue	sunny	
    06.10.2013	20	blue	sunny	
    10.05.2014	5	green	sunny	

    Again I like to have the total running sum. This time for each month and for each combination of colour and weather.

    Year	Month	Colour	Weather	Custom Run Total
    2011	1	red	sunny	
    2011	1	red	rain	
    2011	1	green	sunny	
    2011	1	green	rain	
    2011	1	blue	sunny	
    2011	1	blue	rain	
    2011	2	red	sunny	
    2011	2	red	rain	
    2011	2	green	sunny	
    2011	2	green	rain	
    2011	2	blue	sunny	
    2011	2	blue	rain	
    

    I think it`s a different problem an I have to open a new thread ;-). Have I?



    <link href="moz-extension://c7431ef4-9b71-4af4-bcbb-ce457f5bc3b0/skin/s3gt_tooltip_mini.css" rel="stylesheet" type="text/css" /><style media="print" type="text/css">#s3gt_translate_tooltip_mini { display: none !important; }</style>
    • Edited by Michael8412 Saturday, January 5, 2019 1:17 PM
    Saturday, January 5, 2019 1:10 PM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Running Sum.
    No code needed with PowerPivot.
    Easier to modify as more facets of your real data come to light.
    http://www.mediafire.com/file/ccok7jx23xc83bc/01_05_19.xlsx/file
    http://www.mediafire.com/file/nuqm42cx46z51dc/01_05_19.pdf/file

    Saturday, January 5, 2019 5:08 PM
  • Thank you, but the running sum in your data ends at the end of 2017 and starts at 0 in 2018. I like to have the running sum 6.316 at the end of 01 2018, for example.

    And how can I eliminate values that are invalid?

    Saturday, January 5, 2019 6:35 PM
  • To keep running for two or more years,
    just tweak the calendar query step "Month" some.
    As for "invalid values", I defer to Lz.
    http://www.mediafire.com/file/u600r6v6n49y1l1/01_05_19c.xlsx/file
    http://www.mediafire.com/file/p7j21i441getc9t/01_05_19c.pdf/file

    Sunday, January 6, 2019 3:24 AM