Split row every monday at 6am

[lirakoto]

Hello,

I'm new on power BI. I have a project that count the duration of downtime every week. Our week start on monday at 6am.

I would like to split the row like on the picture bellow every monday at 6am every week.

I already did but I split it per day at 12am. It's not accurate and the size increase.

Could you please help me.

[slorin]

Hi,

Another solution

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcxLDoAgDIThq5iuSRha5NGrEJacQO4fUYndzv9lWiOcHskzWA6wxkqOQvynkJV5TXNcM1B3yxeLoni92EVRztvz55PFqngiql0klbi9UO83", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, finish = _t, Comment = _t]),
Type = Table.TransformColumnTypes(Source,{{"start", type datetime}, {"finish", type datetime}}),
First_Week = Table.AddColumn(Type, "First week", 
    each DateTime.From(Date.StartOfWeek([start]-#duration(0,6,0,0),Day.Monday))+#duration(7,6,0,0), type datetime),
Last_Week = Table.AddColumn(First_Week, "Last Week", 
    each DateTime.From(Date.StartOfWeek([finish]-#duration(0,6,0,0),Day.Monday))+#duration(0,6,0,0), type datetime),
List_Week = Table.AddColumn(Last_Week, "List_Week", 
    each List.DateTimes([First week],Duration.Days([Last Week] - [First week])/7+1,#duration(7,0,0,0))),
Table = Table.AddColumn(List_Week, "Table", 
    each Table.FromColumns({{[start]}&[List_Week],[List_Week]&{[finish]}},type table [start= datetime,finish= datetime])),
Columns = Table.SelectColumns(Table,{"Comment", "Table"}),
Expand = Table.ExpandTableColumn(Columns, "Table", {"start", "finish"}, {"start", "finish"}),
Type_DateTime = Table.TransformColumnTypes(Expand,{{"start", type datetime}, {"finish", type datetime}}),
Duration = Table.AddColumn(Type_DateTime, "Duration", 
    each Duration.TotalMinutes([finish] - [start])/60, type number)
in
Duration

Stéphane

[BA_Pete]

Hi Stephane,

Interesting solution. Could you give a quick rundown of why you believe your solution is preferable to the one already provided please? Is it faster, simpler, using only GUI functions etc.?

Thanks,

Pete

[slorin]

Bonjour Pete

Je réponds en français car mon niveau d'anglais est insuffisant, j'espère que vous me comprendrez 

Je n'ai pas testé ma proposition sur une grande masse de données et je ne sais pas si elle est plus rapide, plus efficace que la vôtre. 

Je prends les questions posées comme de petits défis que je tente de résoudre. Le chemin employé est différent mais on arrive au même résultat. Je n'ai pas d'objectif de "compétition" pour proposer une "meilleure" solution que d'autres. 

Mon idée est de montrer qu'il existe plusieurs voies pour résoudre un problème.

Cela me permet également de transmettre ce que j'ai appris gràce à des forums comme celui-ci. Le langage M est riche et offre de nombreuses possibilités.

Merci à vous

Stéphane

[lirakoto]

Hello @BA_Pete,

Thanks for the answer. It's working very well to split the row but I will try to adjust a little bit to have right date time for the start and finish. It's already a good begining. Thank you.

[BA_Pete]

Hi @lirakoto ,

You shouldn't really need to adjust anything, it's just a case of choosing the columns you want to keep in the last 'remOthCols' step. If you want to keep the actual segment datetimes, then just keep columns [segmentStart] and [segmentEnd]:

You can just delete the 'remOthCols' step completely if you wat, then you'll see all the columns available to select.

Pete

[BA_Pete · Accepted solution]

Hi @lirakoto ,

Paste the following code into a new blank query. I've left the steps separate so you can follow through what each stage is doing, but you could easily condense it into one or two steps at a later date if required:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3JDYAwDARbQX5HYWPndCsRTyqA/oURh18rrWfWcxLKiroyWBaw5qEABUr5b1NT5qc99+NMliyIpdAWTO8OiuLTxUe7cnOd72sqUR67Omfqy2H4ZlXJbotlGzHb7+0C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, finish = _t, comment = _t, durationHour = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"start", type datetime}, {"finish", type datetime}, {"comment", type text}, {"durationHour", type number}}),

    // Relevant steps from here ---->
    addHoursList =
        Table.AddColumn(
            chgTypes,
            "hoursList",
            each let
                __startHour = DateTime.From(Number.RoundDown(24 * Number.From([start]) / 1 ) / 24),
                __stopHour = DateTime.From(Number.RoundUp(24 * Number.From([finish]) / 1 ) / 24)
            in
                List.DateTimes(
                    __startHour,
                    Duration.TotalMinutes(__stopHour - __startHour) / 60,
                    #duration(0,1,0,0)
                )
        ),
    addSegmentEnd =
        Table.AddColumn(
            addHoursList,
            "segmentEnd",
            each List.Combine(
                {
                    List.Select(
                        [hoursList],
                        each Date.DayOfWeek(_, Day.Monday) = 0 and Time.Hour(_) = 6
                    ),
                    {[finish]}
                }
            )
        ),
    expandSegmentEnd = Table.ExpandListColumn(addSegmentEnd, "segmentEnd"),
    addSegmentStart =
        Table.AddColumn(
            expandSegmentEnd,
            "segmentStart",
            each if [start] > Date.AddDays([segmentEnd], -7) and [segmentEnd] <> [finish] then [start]
                else if [finish] = [segmentEnd] then List.Max({Date.StartOfWeek([segmentEnd], Day.Monday) + #duration(0,6,0,0), [start]})
                else Date.AddDays([segmentEnd], -7)
        ),
    addDurationHoursCalc = Table.AddColumn(addSegmentStart, "durationHoursCalc", each Duration.TotalMinutes([segmentEnd] - [segmentStart]) / 60),

    remOthCols = Table.SelectColumns(addDurationHoursCalc,{"start", "finish", "comment", "durationHoursCalc"})
in
    remOthCols

Example query output:

Pete