Expand Dates and Times

[Anonymous]

Hello,

I have a data set as below:

I need to expand the Minutes with respective to date and Value.

[Ashish_Mathur · Accepted solution]

Hi,

This Mo code gets close but returns 2 additional rows (see last 2 rows).  I cannot spot my error

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date and Time", type datetime}, {"End Date and Time", type datetime}, {"Value", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Break out rows", each 60*24*([End Date and Time]-[Start Date and Time])/30),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Break out rows", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.DateTimes([Start Date and Time], Duration.Hours([End Date and Time]-[Start Date and Time])+[Break out rows], #duration(0,0,30,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start Date and Time", "End Date and Time", "Break out rows"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"})
in
    #"Reordered Columns"

Hope this helps.

[Anonymous]

Hi,

Thank you for your Post.

I did one small change in the code as bold lettered below and got the final Output. It works!!!

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date and Time", type datetime}, {"End Date and Time", type datetime}, {"Value", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Break out rows", each 24*(60*([End Date and Time]-[Start Date and Time]))/30),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Break out rows", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.DateTimes([Start Date and Time], ([Break out rows]+1), #duration(00,00,30,00))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Start Date and Time", "End Date and Time", "Break out rows"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type datetime}})
in
#"Changed Type2"

[Ashish_Mathur]

Thank you for sharing this.  By increasing the count by 1, how are the number of rows reducing?  I do not understand.

[Anonymous]

Break out rows = 24*(60*([End Date and Time]-[Start Date and Time]))/30

This Custom column basically calculates the count of 30 mins interval between two times. For Ex: if start time is 3:30 AM and end time is 5:00 AM, the output in Break out rows is 3 ( 4:00, 4:30, 5:00).

Custom = List.DateTimes([Start Date and Time], ([Break out rows]+1), #duration(00,00,30,00))

The above function will list the date with time. First value is the Start time and the count of rows it should list is Break out rows + 1 and the interval is 30mins.

so the output will times as below:

3:30

4:00

4:30

5:00

Note: I didn't mention dates here to illustrate. Hope this helps!

[v-diye-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

[Ashish_Mathur]

Thank you.

[Anonymous]

Thanks for your response. Could you please explain me the steps applied?

That extra rows were there couldn't remove. Please help me with steps so that I can have better understanding and transform the dataset to get the desired output without extra rows.

[Ashish_Mathur]

Hi,

Kinldy click on each step in the Applied steps box and try to understand them yourself.

[smpa01]

You can use List.DateTimes. The syntax of List.DateTimes is (start as datetime, count as number, step as duration). Start as daytime is the Start Datetime. Count= how many 30 minutes interval occur between start and finish date time. For step you need to use #duration. You will have what you need woth this.