Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not 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      |      Name

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

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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Not applicable

@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
Not applicable

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
Not applicable

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

Anonymous
Not 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)

 

image.png

Anonymous
Not applicable

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

 

image.png

Anonymous
Not applicable

@Anonymous  "you help me a lot" 😉

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors