Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Expand Dates and Times

Hello,

 

I have a data set as below:

 

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

9 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    7 years ago
    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.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

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

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

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