Calculate present and next value

[Anonymous]

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      |      Name

10:15:00              |     10:15:30          |       A

10:16:30              |    10:18:24           |       A

09:01:30              |    09:02:43          |       B

[Anonymous · Accepted solution]

try with this.

let
    rr = Table.Group(yourTab, {"Group"}, {{"Breaks", each brksGroup(_), type table}}),
    tbe = Table.ExpandTableColumn(rr, "Breaks", {"List.Breaks"}, {"List.Breaks"})
in
    tbe

where, 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
    breakTab

PS

the code intends to produce a break list, in case of multiple interruptions

[Anonymous]

@Anonymous  thanks for your answer 🙂 Can you help me one last time. I'm new user of power BI and I don't know exacly where I should put this code into my report?

[Anonymous]

Hi, @Anonymous ,

I don't know how exctly give you help you need.

I'm also new on Power BI.

I can tell you how I done the job.

I load the table in power query and the wrote the code you seen in the previuos message to elaboarate the data.

I could load the excel file with the full code here, but I can't see how they load the files here.

[Anonymous]

@Anonymous  okey, but where can I put this code in power query? Is there any option to do this?

[Anonymous]

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)

[Anonymous]

Sorry, I forgot to mention a fundamental premise. Before applying the formula, order the table.
First with group and second with start.

[Anonymous]

@Anonymous  "you help me a lot" 😉