Convert SQL Code Using SUMMARIZE Column

[Lightup]

I have a table like an image below:

Because of the logic to implement, I am to convert it to a table using SUMMARIZE Column. 

I was able to do this using SQL to implement the logic like so:

CREATE TABLE EventLog 
(
  EventID INT
, EventName VARCHAR(50) NOT NULL
, EventStartDateTime DATETIME NOT NULL
, EventEndDateTime   DATETIME NULL 
)


INSERT INTO EventLog(EventID, EventName, EventStartDateTime, EventEndDateTime)
VALUES(100, 'Planting', '20210620 10:34:09 AM','20211018 10:54:49 PM')
 ,(200, 'Foundation', '20200420 10:34:09 AM','20211018 10:54:49 PM')
 ,(300, 'Seeding', '20210410 10:27:19 AM','')
 ,(400, 'Spreading', '20220310 10:24:09 PM','');
 

And I have a query like the below against the above table that I want to convert to a table in Power BI using SUMMARIZE Column

;WITH CTE AS (
   SELECT EventID,EventName,EventStartDateTime,IIF(EventEndDateTime = '',GETUTCDATE(),EventEndDateTime) EventEndDateTime
   FROM EventLog
   UNION ALL
   SELECT EventID,EventName, DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) , EventEndDateTime
   FROM CTE 
   WHERE  DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0)  <= EventEndDateTime
)

SELECT EventID,EventName,YEAR(EventStartDateTime),DATENAME(MONTH,EventStartDateTime),DATEDIFF(second, EventStartDateTime, n_EventStartDateTime) / 3600.0
FROM (
  SELECT EventID,EventName,EventStartDateTime,LEAD(EventStartDateTime,1,EventEndDateTime) OVER(PARTITION BY EventID,EventName ORDER BY EventStartDateTime) n_EventStartDateTime
  FROM CTE
) t1

This gives me result like: 

My requirement is to convert the solution into a table in Power BI. I attempted to use SUMMARIZE Column to get it done but  I am currently not getting it to reproduce the same result yet. I will appreciate any help on this. 

Files attached

[CNENFRNL]

Not surprising that you failed in achieving that goal with simply SUMMARIZE() in DAX; it's purely a data shaping task where PQ is supposed to come into play! Of coz, complex enough even for PQ.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCgIxEESvMmQ9LdWd9pcDuBZchiwCE2RAMiLj/c0HcbbuGurVq/beMGBGc33EvM75Xk6BMOFAgoHhrDqcd2hQSxjEp0HE7dVpT8LoS1aJy/LOU1znJXccBP1TZBtxS2navqMFrxY5Ov5aGq4df75S/BWEYGuhyGUza0L4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EventID = _t, EventName = _t, EventStartDateTime = _t, EventEndDateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EventID", Int64.Type}, {"EventName", type text}, {"EventStartDateTime", type datetime}, {"EventEndDateTime", type datetime}}),
    #"Replaced null" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"EventEndDateTime"}),

    // essential step to replicate the logic of the recursive cte
    n_Interval = Table.AddColumn(
        #"Replaced null",
        "n_Month",
        each let
                diff_yr = Date.Year([EventEndDateTime])-Date.Year([EventStartDateTime]),
                diff_m = Date.Month([EventEndDateTime])-Date.Month([EventStartDateTime]),
                n = diff_yr*12+diff_m, l = List.Accumulate({1..n},{[EventStartDateTime]},
                (s,c) => s & {Date.StartOfMonth(Date.AddMonths([EventStartDateTime],c))}),
                cols = {l, List.Skip(l)&{[EventEndDateTime]}}
            in Table.FromColumns(cols, {"Year","Month"})
    ),

    #"Expanded n_Month" = Table.ExpandTableColumn(n_Interval, "n_Month", {"Year","Month"}),
    TotalHours = Table.AddColumn(#"Expanded n_Month", "Hours", each Number.Round(Duration.TotalHours([Month]-[Year]),2)),
    #"Transformed Cols" = Table.TransformColumns(TotalHours, {{"Year", each DateTime.ToText(_, "yyyy")}, {"Month", each DateTime.ToText(_, "MMMM")}}),
    #"Removed Columns" = Table.RemoveColumns(#"Transformed Cols",{"EventStartDateTime", "EventEndDateTime"})
in
    #"Removed Columns"

[Lightup]

Hello @CNENFRNL 

Thank you for the post. I must say that I really love this. The result is not 100 per cent perfect though. For example, hours in March and July are 744 respectively as their end dates were still open throughout that month. 

[speedramps]

Please can you provide an example table of input data

and an example table of desired output with a brief description.

Rather than copy and paste SQL code. Thanks. 😀