cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvest Frequent Visitor
Frequent Visitor

speed up find closest earlier date in another table

I know this question has been asked in at least two other posts, but I believe my case is slightly different, also because it has to do with performance. I have two tables, one of sensor measurements [SensorData] the other of GPS data [Position]. They each have timestamps, and I am trying to find the GPS location that corresponds with a sensor measurement, based on the timestamp. This means I want to find the timestamp of a GPS-record that is closest (but earlier) in time to the timestamp of the sensor measurement. I would like to add this as a new column to the [SensorData] table, so I can add a relation to the two tables based on this column.

 

Table [Position]:

[Id]  [Timestamp] [Lon] [Lat]

 

Table [SensorData]:

[ID] [SensorID] [Timestamp] [Data]

 

So far I have added a VLOOKUP custom function from the example in this blog post, but it is very slow (i'm handling several hunderds of thousands of records). I guess it is because for each record in [SensorData] the function gets called, which sorts [Position], and compares all elements in [Position] and eventually only takes the first element. I know this can be sped up considerably, but I lack the Power Query skills to actually do it.

 

My query for the [SensorData] table is as follows:

 

 

let
    Source = Sql.Databases("some_link.net"),
    #"SQL-01" = Source{[Name="SQL-01"]}[Data],
    dbo_mydb = #"SQL-01"{[Schema="dbo",Item="mydb"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_mydb, each ([SensorId] = 3)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([Data], ",")),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Filtered Rows1", {{"Data", type number}}, "en-US"),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type with Locale", each [Timestamp] > #datetime(2017, 7, 1, 0, 0, 0)),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows2", "pqVLOOKUP", each pqVLOOKUP([Timestamp], Position, 2, null)),
in
    #"Invoked Custom Function"

 

Any suggestions / improvements?

 

 

1 REPLY 1
Super User IV
Super User IV

Re: speed up find closest earlier date in another table

Does this have to be done in Power Query? I am just wondering if it might be faster in DAX as a calculated column. I would think that some combination of calculating the MAX of Timestamp with the right filters would get you there.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors