Dismembering Lines by hour

[andersoncrh]

Hello guys, I have a database kinda like this with thousands of lines

Game Name	Machine Number	Date	Starting Time	Ending Time
RDR2	3	18/03/2024	00:21:07	06:32:33
LOL	1	18/03/2024	16:07:20	16:50:00

And I want to know if there is a way or even if it's viable to use power query editor to transform or create a secondary table dismembering line by line to have the following result

Game Name	Machine Number	Date	Starting Time	Ending Time
RDR2	3	18/03/2024	00:21:07	00:59:59
RDR2	3	18/03/2024	01:00:00	01:59:59
RDR2	3	18/03/2024	02:00:00	02:59:59
RDR2	3	18/03/2024	03:00:00	03:59:59
RDR2	3	18/03/2024	04:00:00	04:59:59
RDR2	3	18/03/2024	05:00:00	05:59:59
RDR2	3	18/03/2024	06:00:00	06:32:33
LOL	1	18/03/2024	16:07:20	16:50:00

The hour columns are General types.

In other words, I want to create a new row when the duration time(Ending Time - Starting Time) + Starting Time(just minutes and seconds) is greater than 60 minutes.

[Anonymous · Accepted solution]

Hi @andersoncrh ,

I have create a simple sample, please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcoxCsAwDEPRu2gORLbbtHjuGAhkNb7/NZrQrYPgC14E5jMVBbYmd6VVpR7rkK7ivHY2N3UzZAn00Tf9c2nLuvLLk04i8wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Game Name" = _t, #"Machine Number" = _t, Date = _t, #"Starting Time" = _t, #"Ending Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Game Name", type text}, {"Machine Number", Int64.Type}, {"Date", type text}, {"Starting Time", type time}, {"Ending Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(0,Duration.Hours([Ending Time]-[Starting Time])+1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Ending", each let a=List.Max(Table.SelectRows(#"Changed Type1",(x)=>x[Game Name]=[Game Name] and x[Machine Number]=[Machine Number])[Custom]),
b=if [Custom]=a then [Ending Time] else #time(
Time.Hour([Starting Time])+[Custom],59,59)
in b),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [Custom]=0 then [Starting Time] else #time(Time.Hour([Starting Time])+[Custom],0,0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Game Name", "Machine Number", "Date", "Starting Time", "Ending Time", "Custom", "Custom.1", "Ending"})
in
    #"Reordered Columns"

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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