Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 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
    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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 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
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.