How to Split some rows of a table based on hours worked

[somnath6309]

Hi,

Consider the following Data Model.

A worker starts a shift that has a given duration. Date and Time Starts is when the Shift starts. Hours Worked is the shift duration.  Amount is the corresponding Cost. 

Now we have to present the folloiwng report :

Now if we see the folloiwng fact table we will discover that on the Month End Date, TimeStart was at 09:00 PM and Hours worked was 09 hours. Hence a worker worked some hours on 31st Jan and some hours on 01st Feb. 

Now the desired output is the following where we want to show dates which are not in the Fact Table and Split working hours at the right way:

To do that , We have to change the Granularity of the Fact Table. The Date represents when the event started. If you want to split by date then you can split an event : if the event crossed the date, you split it in two events: 

• Half of it when it started
• The remaining Part in the next day. 

The following is the requirement which requires M Code :

The finished file is attached herewith in the Link.

However, how the same was achieved has not been shown in the article. The Mcode contains some Formulas, Syntaxes that are hard to understand for one who does not know it. 

Pls provide a detailed procedure to achieve the above out put. 

Regards,

Somnath6309

https://1drv.ms/u/c/9b5853c19a1e2017/EfQGeYu0VmFOiQgY_OKWUrkBehe9HnwScyecc047gp3W_w?e=oYoGRq 

[v-saisrao-msft · Accepted solution]

Hi @somnath6309,

Thank you for your follow-up and below is the explanation of the query 
Step 1 - Added Custom: End DateTime: 

• DateTime.From: Converts a text string to a DateTime value in Power Query. 

• Text. From: Merges the Date and Time fields into one text string, such as "2024-07-01 09:00:00". 

• #duration: Adds the number of hours worked to the start time, subtracting 1 second to ensure the end time does not extend into the next day (e.g., ending at 6:00:00 instead of 6:00:01). 

Q-2: Purpose of the functions:  

• Duration.Days: Calculates the total number of days between the start and end dates.  

• DateTime.Date: Extracts only the date component from a datetime value, removing the time.  

• Why is "+1" added at the end of the formula? 

• This is to make the range inclusive, so both the start and end dates are counted. For instance, if a shift starts and ends on the same day, NumOfDays should be 1. 

Q-3: List.Dates 

• List.Dates: Generates a list of dates. 

• DateTime.Date: start time 

Step 4: AddedCustom - 4: Custom 

• This column determines the number of hours worked on each specific date within a shift. If the shift occurs within a single day, it returns the total HoursWorked. For shifts spanning multiple days, it allocates hours by calculating the remaining hours on the start date (24 minus the start hour) and, on the end date, adds 1 second to the end time to address rounding before extracting the hour. This approach accurately assigns hours to each relevant date. 
   

If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-saisrao-msft]

Hi @somnath6309,

We haven’t heard back from you in a while. If your issue has been resolved, Mark the relevant response as the solution to help fellow community members.
If you still need support, let us know if you still need assistance.

Thank You.

[v-saisrao-msft]

Hi @somnath6309,

I hope you had a chance to review the solution shared by @AlienSx @mromain . If it addressed your question, please consider accepting it as the solution — it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[somnath6309]

Hi,

Thanks for the help. However, as a new user of Power Query I will not be able to implement these solution. I have studied the attached PBIX file and broke down the steps. It will be very helpful for all if we get the explanation of the following functions :

Step: 1 - Added Custom : EndDateTime

Q-1: The purpose of the functions:

01. DateTime.From

02. Text.From

03. #duration - is the name of the function starts with "#" ? What is the syntax of the function ? within in bracket we find 0, [HoursWorked], 0, -1 and the same is not understood without syntax break up. 

Step: 2 - Added Custom2 - Number of Days

Q-2: The Purpose of the functions :

01. Duration.Days

02. DateTime.Date
03. Why "+1" is getting added at the end of the formula ?

Step 3: Added Custom 3 : Dates

Q-3 : List.Dates

What is the purpose of the fuction : List.Dates ? What are its syntax ?

with in the function, DateTime.Date function is used as an argument. Then [NumOfDays], a filed name is used. And lastly #Duration (1,0,0,0) is used. 

Pls provide a detailed discussion for the above formula else the same cannot be usable without a proper understanding. 

Step 4 : AddedCustom - 4 : Custom

Pls discuss the highlighted formula :

Regards,

Somnath6309

[v-saisrao-msft]

Hi @somnath6309,

checking in to see if your issue has been resolved. If the response provided was helpful, kindly mark it as the solution so that others with the same issue can benefit.
let us know if you still need assistance.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @somnath6309,

I hope you had a chance to review the solution shared earlier. If it addressed your question, Consider accepting it as the solution it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @somnath6309,

Thank you for your follow-up and below is the explanation of the query 
Step 1 - Added Custom: End DateTime: 

• DateTime.From: Converts a text string to a DateTime value in Power Query. 

• Text. From: Merges the Date and Time fields into one text string, such as "2024-07-01 09:00:00". 

• #duration: Adds the number of hours worked to the start time, subtracting 1 second to ensure the end time does not extend into the next day (e.g., ending at 6:00:00 instead of 6:00:01). 

Q-2: Purpose of the functions:  

• Duration.Days: Calculates the total number of days between the start and end dates.  

• DateTime.Date: Extracts only the date component from a datetime value, removing the time.  

• Why is "+1" added at the end of the formula? 

• This is to make the range inclusive, so both the start and end dates are counted. For instance, if a shift starts and ends on the same day, NumOfDays should be 1. 

Q-3: List.Dates 

• List.Dates: Generates a list of dates. 

• DateTime.Date: start time 

Step 4: AddedCustom - 4: Custom 

• This column determines the number of hours worked on each specific date within a shift. If the shift occurs within a single day, it returns the total HoursWorked. For shifts spanning multiple days, it allocates hours by calculating the remaining hours on the start date (24 minus the start hour) and, on the end date, adds 1 second to the end time to address rounding before extracting the hour. This approach accurately assigns hours to each relevant date. 
   

If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[somnath6309]

Hi @v-saisrao-msft :

Thanks for the help. Accepted as solution.

Regards,

Somnath6309

[AlienSx]

The Definitive Guide to Power Query (M) and this web site are your best friends for the foreseeable future.

[AlienSx]

let
    data_columns = {"Date", "TimeStart", "HoursWorked"}, 
    fx_split = (data as list) => ((end) => List.Generate(
        () => [
            Date = data{0}, 
            TimeStart = data{1}, 
            e = List.Min({Date.EndOfDay(Date & TimeStart), end}),
            HoursWorked = Duration.TotalHours(e - (Date & TimeStart)),
            hours_left = data{2} - HoursWorked
        ],
        (x) => x[HoursWorked] > 0,
        (x) => [
            Date = Date.AddDays(x[Date], 1), 
            TimeStart = #time(0, 0, 0), 
            e = List.Min({Date.EndOfDay(Date & TimeStart), end}),
            HoursWorked =  Duration.TotalHours(e - (Date & TimeStart)),
            hours_left = x[hours_left] - HoursWorked
        ], 
        (x) => Record.SelectFields(x, data_columns)
    ))(data{0} & data{1} + #duration(0, data{2}, 0, 0)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBRCsAgCIavMnwOlrai9rYDDPYe3f8a04gxoSJQMPj4vzRnQDBgcefCIGM6rd2um8fIjcFCMR+FvlFBqEcoeWJUlFNZlUrcrmXRkpGGRi+UV1THKFmOfv+invEQUkEdoZMFFERp6qOhr15LUysbzo3lBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkerId = _t, Date = _t, TimeStart = _t, HoursWorked = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkerId", Int64.Type}, {"Date", type date}, {"TimeStart", type time}, {"HoursWorked", Int64.Type}, {"Amount", Int64.Type}}),
    combine = Table.CombineColumns(#"Changed Type", data_columns, fx_split, "x"), 
    xpand_list = Table.ExpandListColumn(combine, "x"), 
    result = Table.ExpandRecordColumn(xpand_list, "x", data_columns)
in
    result

[mromain]

Hi somnath6309,

Here a possible solution:

let
    Source = #table(
        type table [WorkerId = Int64.Type, Date = date, TimeStart = time, HoursWorked = Int64.Type, Amount = Int64.Type],
        {
            {1, #date(2016, 1, 1), #time(9, 0, 0), 8, 160},
            {1, #date(2016, 1, 15), #time(18, 0, 0), 6, 180},
            {1, #date(2016, 1, 31), #time(21, 0, 0), 9, 360},
            {2, #date(2016, 1, 1), #time(9, 0, 0), 8, 160},
            {2, #date(2016, 1, 15), #time(18, 0, 0), 5, 150},
            {2, #date(2016, 1, 31), #time(21, 0, 0), 8, 320},
            {1, #date(2016, 2, 1), #time(9, 0, 0), 4, 80},
            {1, #date(2016, 2, 15), #time(18, 0, 0), 3, 90},
            {1, #date(2016, 2, 29), #time(21, 0, 0), 8, 320},
            {2, #date(2016, 2, 1), #time(9, 0, 0), 6, 120},
            {2, #date(2016, 2, 15), #time(18, 0, 0), 5, 150},
            {2, #date(2016, 2, 29), #time(21, 0, 0), 8, 320}
        }),
 
    TransformSource = 
        let
           fnTransformRecord = (r) =>
                let
                    From = DateTime.From(Number.From(r[TimeStart]) + Number.From(r[Date])),
                    To = From + #duration(0, r[HoursWorked],0,0),
                    ListDates = List.Transform({Number.From(Date.From(From)) .. Number.From(Date.From(To)) - (if Time.Hour(To) = 0 then 1 else 0)}, Date.From),
                    baseResult = Record.RemoveFields(r, {"Date", "TimeStart", "HoursWorked"})
                in
                    List.Transform(ListDates, each let timeStart = if _ = Date.From(From) then Time.From(From) else #time(0,0,0) in baseResult & [Date = Date.From(_), TimeStart = timeStart, HoursWorked = (if _ = Date.From(To) then Time.Hour(Time.From(To)) else 24) -Time.Hour(timeStart)]),
            TransformData = Table.FromRecords(List.Combine(List.Transform(Table.ToRecords(Source), fnTransformRecord))),
            ReorderColumns = Table.ReorderColumns(TransformData,Table.ColumnNames(Source))
        in
            Value.ReplaceType(ReorderColumns, Value.Type(Source)) 
in
    TransformSource