I wonder if there is a way to get a second table ex:

5/4/2020 0:00 -> Duration of the 1st event

5/5/2020 9:16 -> Duration of the 2st event

Where each durantion of the event is given by calculating the difference between time n and time n 1.

CodePudding user response：

You can try below

I assume the first column is a text formatted column that can be split on a space to get the numbers at the end

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Custom", type date}, {"Hour", Int64.Type}, {"Day of Week", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "dur1"}),
// pull row above into current row
    shiftedList = {null} & List.RemoveLastN(#"Split Column by Delimiter"[dur1],1),
    custom1 = Table.ToColumns(#"Split Column by Delimiter") & {shiftedList},
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Split Column by Delimiter") & {"Prev Row"}),
#"Changed Type1" = Table.TransformColumnTypes(custom2,{{"dur1", type duration}, {"Prev Row", type duration}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom.1", each [dur1]-[Prev Row]),
Alternate=Table.AlternateRows(#"Added Custom",0,1,1),
#"Removed Columns" = Table.RemoveColumns(Alternate,{"dur1", "Prev Row", "Column1.1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.1", type duration}})
in #"Changed Type2"