Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Everyone,
I need to add a column "Total Time" as running total for the "Time" column until the next Item change. I think I can do this with List.Accummulate but I'm not sure how to use this function.
Appreciate if someone in this forum can help me. Thank you very much.
Item | STAGE | TIME | TOTAL TIME |
CX229 | A | 5 | 5 |
CX229 | B | 10 | 15 |
CX229 | C | 15 | 30 |
DA051 | A | 7 | 7 |
DA051 | B | 8 | 15 |
DA051 | D | 3 | 18 |
DA051 | E | 6 | 24 |
DA214 | B | 9 | 9 |
DA214 | C | 15 | 24 |
DA214 | G | 5 | 29 |
DA214 | H | 3 | 32 |
Solved! Go to Solution.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
data = List.Buffer(Table.ToRows(Source)),
gen = List.Generate(
() => [i = 0, item = data{0}{0}, total = data{0}{2}],
(x) => x[i] < List.Count(data),
(x) => [i = x[i] + 1, item = data{i}{0}, total = data{i}{2} + x[total] * Number.From(item = x[item])],
(x) => data{x[i]} & {x[total]}
),
tbl = Table.FromRows(gen, Table.ColumnNames(Source) & {"TOTAL TIME"})
in
tbl
Hi @Emily_Kim Go to power query and add a index column. See image below:
Go to home tab and click advance edition and add the below marked line. Using List.Sum and List.FirstN function you can acheive your desired result. See image below:
You will get your desired output. See image below:
Now you can remove index column and change type of running total.
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
Check this one step code:
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(X, "Custom", each List.Sum(Table.SelectRows(X, (a)=> a[Index]<=[Index] and a[Item]=[Item])[TIME]))
in
#"Added Custom"
Check this one step code:
let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
X = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(X, "Custom", each List.Sum(Table.SelectRows(X, (a)=> a[Index]<=[Index] and a[Item]=[Item])[TIME]))
in
#"Added Custom"
Hi @Emily_Kim Go to power query and add a index column. See image below:
Go to home tab and click advance edition and add the below marked line. Using List.Sum and List.FirstN function you can acheive your desired result. See image below:
You will get your desired output. See image below:
Now you can remove index column and change type of running total.
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
data = List.Buffer(Table.ToRows(Source)),
gen = List.Generate(
() => [i = 0, item = data{0}{0}, total = data{0}{2}],
(x) => x[i] < List.Count(data),
(x) => [i = x[i] + 1, item = data{i}{0}, total = data{i}{2} + x[total] * Number.From(item = x[item])],
(x) => data{x[i]} & {x[total]}
),
tbl = Table.FromRows(gen, Table.ColumnNames(Source) & {"TOTAL TIME"})
in
tbl
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
17 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
27 | |
16 | |
14 | |
14 |