Forum Discussion
Anonymous
6 years agoNot applicable
Calculate present and next value
Hey guys 🙂 I want to calculate break between starting date and ending date by only if name are equals. ( 10:16:30 - 10:15:30 = 00:01:00) e.g. Starting date | Ending date | ...
- Anonymous6 years ago
try with this.
let rr = Table.Group(yourTab, {"Group"}, {{"Breaks", each brksGroup(_), type table}}), tbe = Table.ExpandTableColumn(rr, "Breaks", {"List.Breaks"}, {"List.Breaks"}) in tbewhere, brkGroup is the following code.
let breakTab=(tab as table) => let tabSorted=Table.Sort(tab,{"Start"}), breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Start]{c}-tabSorted[End]{c-1}}) in Table.FromColumns({breakList},{"List.Breaks"}) in breakTabPS
the code intends to produce a break list, in case of multiple interruptions
Anonymous
6 years agoNot applicable
Anonymous okey, but where can I put this code in power query? Is there any option to do this?
Anonymous
6 years agoNot applicable
If you don't know how to use the power query code, I think it's better to "work" in another environment. Excel, for example.
I attach the image of the sheet where there are data and formulas. "SE" in the formula space is the italian version of "IF", considere also the need to change ";" (semicolon) in the formulas with ","(colon)
- Anonymous6 years agoNot applicable
Anonymous "you help me a lot" 😉
- Anonymous6 years agoNot applicable
Sorry, I forgot to mention a fundamental premise. Before applying the formula, order the table.
First with group and second with start.