cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jswinkert
New Member

query edit, fill down data

 

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



1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Solution Sage
Solution Sage

Re: query edit, fill down data

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!

View solution in original post

2 REPLIES 2
ChrisHaas Solution Sage
Solution Sage

Re: query edit, fill down data

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!

View solution in original post

jswinkert
New Member

Re: query edit, fill down data

Amazing job !

 

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

I appreciate your help.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors