Creating Hourly Matrixes Shift Start and End Dates

[RedDevilsTreble]

Hi,

I hope you're all well.

I have a dataset which I have mocked up to learn PBI and have stumbled into a wall.

The mock data has Shift start date and end date, including the time. I am trying to make a matrix visual which shows the total labour cost over an hour for a restaurant. 

Any ideas or resources of how I could get a matrix visual for the above, detailing total cost over an hour?

[parry2k]

@RedDevilsTreble how would you manually compute it? What is the logic? Please share an example of the calculation.

[RedDevilsTreble]

I would take the total cost and divide it by the hours rendered to give a cost per hour. In this case, I need to split out the hours into hourly intervals as they're currently amalgamated. I have found some M code to do this as per the below as a custom column but it's just giving me 'list' in the column and an error when I expand to rows.

List.Generate(
() => [Start DateTime],
each _ < [End DateTime],
each _ + #duration(0, 1, 0, 0)
)

[Anonymous · Accepted solution]

Hi @RedDevilsTreble ,

Please refer to the following steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZSxbsMwDER/xfAcQCIpy1K3DB0DdA8yZmvRrv37yqRt6RQVMAzcBXwhqZPv95nmyyycy/v96+fz+/f5nEyK49Wx5zDFN+/LM11vYMtuf9zmx2UHrQQgleSiFRC1oNNOyOHyG8vSckwmR+sIVP2OJFtHS4KOVGbHaUSqfkcKGykFIKks2xCsOJa02wuC4jZ4gG2bjI72CvItqforkrKSViRtkh0vo+Gq3w1HXqfDCCSLwEHiWtLYeQCiCGsyWaYgnOIYbrdDR2IlCZLEDlv+CYGMp9M8MTRlspRknOMgZcz9QdpCyLgnk+QdpcFdafz+skRNORyfSXKl6jVR1V4GJBJIuUlyYbDz0+1Xvv15hOur6nUd3Zb6yTQ4GSYz2RwdxKn6XZ5Yvx4EPZksWQ4jVPVHKBEPXaksUebRxat+d/FYs5mxK5Xh+KLBpk63XdTjDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pub ID" = _t, #"Employee Number" = _t, #"Staff Name" = _t, #"Shift Start" = _t, #"Shift End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pub ID", Int64.Type}, {"Employee Number", Int64.Type}, {"Staff Name", type text}, {"Shift Start", type datetime}, {"Shift End", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    Start = [Shift Start],
    End = [Shift End],
    Duration = #duration(0, 1, 0, 0) 
in
    List.Generate(
        () => [CurrentTime = Start], 
        each [CurrentTime] <= End,
        each [CurrentTime = [CurrentTime] + Duration]
    )),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"CurrentTime"}, {"CurrentTime"})
in
    #"Expanded Custom1"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum