query edit, fill down data

[jswinkert]

I have a dataset whicn need a contiguos Time series. The step is 1 minute. For some assets, data is missing. Bellow is a example. Between row 1 and row 2 i need to repeat the line 1, but the time sequence must be: 10:30:00 / 10:31:00 / 10:32:00. 

How can i handle this issue ?

Tks a lot. 

Sample data: 

 Date Time Asset Open High Low Close Vol
6/5/2018 10:30:00 AM PCAR4 79.99 80.42 79.98 80.28 7300
6/5/2018 10:32:00 AM PCAR4 80.36 80.45 79.83 79.83 5100
6/5/2018 10:33:00 AM PCAR4 79.97 80.12 79.94 80.09 8900
6/5/2018 10:34:00 AM PCAR4 80.1 80.21 80.1 80.21 1300
6/5/2018 10:35:00 AM PCAR4 80.15 80.15 80.09 80.11 2000
6/5/2018 10:36:00 AM PCAR4 80.14 80.16 80.09 80.11 1700
6/5/2018 10:37:00 AM PCAR4 80.15 80.47 80.13 80.47 3700
6/5/2018 10:38:00 AM PCAR4 80.42 80.42 80.42 80.42 100
6/5/2018 10:39:00 AM PCAR4 80.41 80.5 80.25 80.37 6400
6/5/2018 10:40:00 AM PCAR4 80.3 80.33 80.15 80.15 1400

[Anonymous · Accepted solution]

Ok, this one is a little involved, but I'll do my best explain it step by step.

In my solution the table of sample data is called RawData.  You'll need to update the code  in the first table as needed when refering to that table.

In short, create 3 new blank queries, and copy and paste this text into the advanced editor for each one.  Be sure to name the tables as I have when you first create them, otherwise they won't reference each other properly.

1st table = Data (staged)

let
    Source = RawData, //Change this to reference whatever you called your table.  Remember if your table name has spaces in it you'll need to refer to it as #"Table Name with spaces"
    Add_DateTime = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime)
in
    Add_DateTime

2nd table = AllTimes

let
    Source = #"Data (staged)",
    MinDateTime = List.Min(Source[DateTime]),
    MaxDateTime = List.Max(Source[DateTime]),
    TotalMinutes = Duration.Minutes(MaxDateTime - MinDateTime),
    MinuteIncrement = #duration(0, 0, 1, 0),
    TimeList = List.DateTimes(MinDateTime, TotalMinutes, MinuteIncrement),
    #"Converted to Table" = Table.FromList(TimeList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime KEY"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime KEY", type datetime}})
in
    #"Changed Type"

3rd table = FinalTable

let
    Source = Table.NestedJoin(#"Data (staged)",{"DateTime"},AllTimes,{"DateTime KEY"},"AllTimes",JoinKind.RightOuter),
    #"Expanded AllTimes" = Table.ExpandTableColumn(Source, "AllTimes", {"DateTime KEY"}, {"DateTime KEY"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllTimes",{{"DateTime KEY", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Date", "Time", "Asset", "Open", "High", "Low", "Close", "Vol"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"DateTime", "DateTime KEY"})
in
    #"Removed Columns"

Data (staged) adds a DateTime column to your original data.  This is just a merge of the two date and time columns.  Since I don't know if your data spans multiple days, I need to ensure we get every minute which could span across multple days.

AllTimes references the first table.  It figures out the min and max datetime, then creates a list of ALL of the datetimes inbetween those 2 values, incremented by 1 minute.  That list is then converted into a table.

FinalTable then joins those 2 tables.  Data (staged) right outer join AllTimes.  This grabs every single time value from the AllTimes table, and the corresponding data (if it exists) from the Data (staged) table.  Since the join step can mix up the order of the resulting table, we need to sort by the datetime column coming from AllTimes (after we expand the nested join to get that table).

Now that we have all of the datetimes in order, you can apply the fill down on all of the other columns, so that the data will copy down into the next minute.

Then just remove the DateTime and DateTime KEY columns to clean everything up.

Hope this helps!

[jswinkert]

Amazing job !

I did same job in Vba, it is very easy. M language is for pros like you. 

I appreciate your help.