• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running total in power query

petter_hoang

New Member
Please help me write a M function in Power Query with the following formula
83658
 

Attachments

  • PQ.xlsx
    11 KB · Views: 3
Last edited:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"Name", type text}, {"v1", Int64.Type}, {"v2", Int64.Type}}),
    Values = List.Buffer(Set_types[v2]),
    Running_totals = List.Generate(
() => [Running = Values{0}, Count = 0],
each [Count] < List.Count(Values),
each [Running = [Running]+ Values{[Count] + 1}, Count = [Count] +1 ],
each [Running]),
    Combine_Values_Running_tot = Table.FromColumns(Table.ToColumns(Set_types) & {Running_totals}, Table.ColumnNames(Set_types) & {"Running"}),
    Add_conditional_sum = Table.AddColumn(Combine_Values_Running_tot, "Running Total", each if [v1]>0 then [v2] else [Running], type number),
    REmove_temp_running_tot = Table.RemoveColumns(Add_conditional_sum,{"Running"})
in
    REmove_temp_running_tot
 

Attachments

  • PQ.xlsx
    20.8 KB · Views: 3
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"Name", type text}, {"v1", Int64.Type}, {"v2", Int64.Type}}),
    Values = List.Buffer(Set_types[v2]),
    Running_totals = List.Generate(
() => [Running = Values{0}, Count = 0],
each [Count] < List.Count(Values),
each [Running = [Running]+ Values{[Count] + 1}, Count = [Count] +1 ],
each [Running]),
    Combine_Values_Running_tot = Table.FromColumns(Table.ToColumns(Set_types) & {Running_totals}, Table.ColumnNames(Set_types) & {"Running"}),
    Add_conditional_sum = Table.AddColumn(Combine_Values_Running_tot, "Running Total", each if [v1]>0 then [v2] else [Running], type number),
    REmove_temp_running_tot = Table.RemoveColumns(Add_conditional_sum,{"Running"})
in
    REmove_temp_running_tot
Thanks a lot @GraH - Guido,

But i want the following result


 

Attachments

  • 1680138547891.png
    1680138547891.png
    14.5 KB · Views: 7
Last edited:
petter_hoang
Please, reread and follow Forum rules:
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Back
Top