Forum Discussion

andersoncrh's avatar
andersoncrh
New Member
2 years ago
Solved

Dismembering Lines by hour

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

 

Game NameMachine NumberDateStarting TimeEnding Time
RDR2318/03/202400:21:0706:32:33
LOL118/03/202416:07:2016: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 NameMachine NumberDateStarting TimeEnding Time
RDR2318/03/202400:21:0700:59:59
RDR2318/03/202401:00:0001:59:59
RDR2318/03/202402:00:0002:59:59
RDR2318/03/202403:00:0003:59:59
RDR2318/03/202404:00:0004:59:59
RDR2318/03/202405:00:0005:59:59
RDR2318/03/202406:00:0006:32:33
LOL118/03/202416:07:2016: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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.