Power Bi question

[MikkoR]

Hey,
I need help with Power BI.

I have a table called Time Periods. The table has three columns, StartTime, EndTime and ID.

I would like to calculate how much time has been spent on each calendar day.

How could I do this?

An example of the content of the table

An example of the result

-Mike

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    min = Number.RoundDown(Number.From(List.Min(Source[StartTime]))),
    max = Number.RoundDown(Number.From(List.Max(Source[EndTime]))),
    lst = {min..max},
    lst1 = List.Transform(lst, each Date.From(_)),
    TimeCalc = (dt, start, end)=> 
        let ns = Number.From(start), ne = Number.From(end), nd = Number.From(dt)
        in  if nd = Number.RoundDown(ns) then List.Min({1-Number.Mod(ns,1), ne-ns}) else 
            if nd > Number.RoundDown(ns) and nd < ne then List.Min({1, ne - nd}) else 0,
    lst2 = Table.ToRows(Table.SelectColumns(Source,{"StartTime", "EndTime"})),
    lst3 = List.Transform(lst1, each 
        {_, List.Accumulate(lst2, 0, (s,c)=> s + TimeCalc(_, c{0},c{1}))}),
    Result = Table.FromRows(lst3, {"Date", "Days"})
in
    Result

Book1.xlsm
	A	B	C	D	E	F	G
1	Table1				Query Output
2	ID	StartTime	EndTime		Date	Days
3	1	1/16/2024 15:57:53	1/19/2024 19:37:39		1/14/2024	0.855954815
4	2	1/19/2024 13:43:00	1/27/2024 23:34:01		1/15/2024	2
5	3	1/16/2024 00:12:50	1/18/2024 10:45:20		1/16/2024	3.325898287
6	4	1/14/2024 05:56:51	1/20/2024 07:42:23		1/17/2024	4
7	5	1/20/2024 19:14:47	1/28/2024 23:51:34		1/18/2024	3.969103368
8	6	1/21/2024 10:24:21	1/26/2024 07:24:57		1/19/2024	4.818597014
9	7	1/14/2024 21:30:35	1/21/2024 17:15:42		1/20/2024	4.80671912
10	8	1/18/2024 11:29:49	1/27/2024 02:07:04		1/21/2024	6.285656794
11	9	1/19/2024 10:15:52	1/25/2024 22:27:24		1/22/2024	5.842862604
12	10	1/20/2024 17:05:55	1/22/2024 20:13:43		1/23/2024	5
13					1/24/2024	5
14					1/25/2024	4.935699028
15					1/26/2024	3.308997593
16					1/27/2024	2.070201968
17					1/28/2024	0.99414235
18
Sheet2

 

[MikkoR]

That is certainly a working solution when the source is Excel. But the source I have is a SQL table that I have imported into Power Bi. I would like to do these actions in the Power Bi application.
So the source table TimePeriods is imported from SQL server

 

[JGordon11]

Change the first step to import from SQL.

 

[MikkoR]

So I have boards imported to Power Bi. I would like that function so that a new board is created with the result. How do I do that?

Tables:

 

[MikkoR]

?