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

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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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)