March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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 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?
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 okey, but where can I put this code in power query? Is there any option to do this?
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)
Sorry, I forgot to mention a fundamental premise. Before applying the formula, order the table.
First with group and second with start.
@Anonymous "you help me a lot" 😉
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |