Convert a line every 30 minutes

[Anonymous]

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?

[Anonymous · Accepted solution]

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.