Forum Discussion
I Need a help Running total in power query
Please I need calculate column acumulative values. (Running t) in base the column (ve) with the variables (Est) and (CICL)
in the example .
PD = I need Mcode
thanks for your help
Copy paste the below code or just open the pbix to check the applied steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKlTSUUoEYkMDpVgdBBeVZ4xPEsJLxsozwSdpik0yBdVKGNcMlYuNB3JPEgbPBJVrhE1tMqraZKyS5hiSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Est = _t, CICL = _t, ve = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Est", type text}, {"CICL", type text}, {"ve", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), BufferedTable = Table.Buffer(#"Added Index"), RunningTotal = Table.AddColumn(BufferedTable, "Running Total", (OutTable) => List.Sum(Table.SelectRows(BufferedTable, (InTable) => InTable[Index] <= OutTable[Index] and InTable[Est] = OutTable[Est] and InTable[CICL] = OutTable[CICL])[ve])) in RunningTotalPaul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
4 Replies
In none of the links is the ejm that is needed. The groups that are accumulating are in the same column, I want to group in 2 different columns- jennratten
Super User
In the sample image in the original post, it looks like you have created a running total, shaded in yellow, of the data in ve and grouped by the values in Est. Are you also trying to create a second column with running totals for the data in ve and grouped by the values in CICL?
Copy paste the below code or just open the pbix to check the applied steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKlTSUUoEYkMDpVgdBBeVZ4xPEsJLxsozwSdpik0yBdVKGNcMlYuNB3JPEgbPBJVrhE1tMqraZKyS5hiSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Est = _t, CICL = _t, ve = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Est", type text}, {"CICL", type text}, {"ve", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), BufferedTable = Table.Buffer(#"Added Index"), RunningTotal = Table.AddColumn(BufferedTable, "Running Total", (OutTable) => List.Sum(Table.SelectRows(BufferedTable, (InTable) => InTable[Index] <= OutTable[Index] and InTable[Est] = OutTable[Est] and InTable[CICL] = OutTable[CICL])[ve])) in RunningTotalPaul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
