Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
somnath6309
Helper I
Helper I

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

Hi,

Consider the following Data Model.

somnath6309_0-1750859310311.png

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 :

 

somnath6309_1-1750859503259.png

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. 

 

somnath6309_2-1750859708079.png

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:

somnath6309_3-1750860174323.png

 

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 :

HoursWorked.png

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 

7 REPLIES 7
v-saisrao-msft
Community Support
Community Support

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.

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

somnath6309_0-1750940019754.png

 

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

somnath6309_1-1750940751906.png

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

somnath6309_2-1750941536177.png

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. 

somnath6309_3-1750941927934.png

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

 

Step 4 : AddedCustom - 4 : Custom

somnath6309_4-1750942363024.png

 

Pls discuss the highlighted formula :

somnath6309_5-1750942441666.png

Regards,

Somnath6309

 

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.

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. 

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

AlienSx
Super User
Super User

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
Regular Visitor

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.