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]

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

[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