Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Convert a line every 30 minutes

Hi, my data looks like a screenshot.

I have to make a matrix in which I will show what the production of my product looks like over time ("Value" column), which means I must be able to estimate how much product will be at a specific time (target matrix every 30 minutes) - also screenshot with excel example.

Any ideaa?

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Here's my solution in Power Query.

    1.Create a table contains the time intervals.

    let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 1, #duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(0, 0, 0), 96, #duration(0, 0, 30, 0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type time}, {"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date"})
in
    #"Removed Columns"

     

    2.Add a custom column to insert the table.

     

    3.Expand the custom column.

     

    4.Add a custom for filtering.

     

    5.Filter out 0 and keep 1.

     

    6.Select the time interval column and pivot below.

     

    7.The result is as follows.

     

     

     

    Best Regards,

    Stephen Tao

     

    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
    3 years ago

    Hi Anonymous ,

     

    Here's my solution in Power Query.

    1.Create a table contains the time intervals.

    let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 1, #duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(0, 0, 0), 96, #duration(0, 0, 30, 0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type time}, {"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date"})
in
    #"Removed Columns"

     

    2.Add a custom column to insert the table.

     

    3.Expand the custom column.

     

    4.Add a custom for filtering.

     

    5.Filter out 0 and keep 1.

     

    6.Select the time interval column and pivot below.

     

    7.The result is as follows.

     

     

     

    Best Regards,

    Stephen Tao

     

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