Forum Discussion

RedDevilsTreble's avatar
RedDevilsTreble
New Member
1 year ago
Solved

Creating Hourly Matrixes Shift Start and End Dates

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?

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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 

3 Replies

    • RedDevilsTreble's avatar
      RedDevilsTreble
      New Member
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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