Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

How to do a running Sum by group in Power Query?

Hi Everyone, I am trying to do a running sum by group in Power Query (m language).  Thank you. All solutions I found was to use DAX which I cannot use for my data at this time.   Here is what my ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    You can use this query (assuming you want to group on "BU"):

     

    let
    Source = Table1,
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"

     

    With function fnAddRunningSum:

     

    (MyTable as table) as table =>
    let
        Source = Table.Buffer(MyTable),
        TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
        Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
        AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
    in
        AddedRunningSum
TheOctopusIAm's avatar
TheOctopusIAm
Frequent Visitor
6 years ago

The line where you call the function includes:

each fn_AddCumul(_,"Data.No  of Tablets per DMU.# Tablets"),

where the second argument is supposed to be a column name.

Is "Data.No  of Tablets per DMU.# Tablets" definitely your column name? At a glance it looks like a combination of a merged column from another table, combined with the name of a previous M step?

mei_mn's avatar
mei_mn
Regular Visitor
6 years ago

Thank you for your reply

I have now changed to the below but I'm still getting the same error 😞

 

let
    Source = Table.Combine({#"DMU Opening", #"DMU Closing"}),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"Hours"}, {{"Data", each Cumul(_,"NumTablets"), type table [Location=text, Opening Day=text, Hours=time, NumTablets=number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Opening Day", "NumTablets","Running Sum"}, {"Location", "Opening Day", "NumTablets","Running Sum"})
in
    #"Expanded Data"

(MyTable as table, MyColumn as number) =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(Cumulative,MyColumn) => Cumulative & {List.Last(Cumulative) + MyColumn})),
    Cumul = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
Cumul