[Help] Join two tables based on dates (with time) between not exact match of two dates.

[asuka]

Hello, I have problem with how to match time in second to time period in power query. It is quite easy in excel using vlookup function, but I don't know how to solve in power query. I had done some search, but most of solutions are joining two tables based on dates matching periods, not suitable for dates with time situation. Any sharing of solutions in power query would be much appreciated!

I need to match time in the 1st table (i.e.11/08/2020 11:43:42 am) to time period in the 2nd table (i.e. start time: 15/08/2020 01:03:04 am, end time: 21/08/2020 09:15:20 pm).

The 1st table is sales table with client purchase information (client name, purchase time). The 2nd table is selling price table with the selling price of product in different time period. The selling price of our product increases every few days at specific time, so the price ranges from certain time to certain time (i.e. start time: 15/08/2020 01:03:04 am, end time: 21/08/2020 09:15:20 pm), the price changes very frequently and there are many many purchases so there are large volume of data.

Many thanks!

[AllisonKennedy]

@asuka  Make sure the two tables are not related by Date, then use this method to lookup within the range: Excel with Allison: DAX Approximate Lookup

You'll need to have a DimProduct table that relates to both tables, and ensure you use that DimProduct[Product] in all DAX and visuals.

Let me know if you get stuck on any steps, as your scenario is slightly more complex than the blog, but not too greatly.

[asuka]

Hi Allison, I tried to create a new measure, but it doesn't work. Could you please help have a look if there is any error? Thanks!

price = calculate(selectedvalue(sell price[sell price]),

FILTER(sell price,sell price[start time]<=sales[purchase time]

&&sell price[end time]>sales[purchase time]))

[AllisonKennedy]

@asuka , sorry, I lost track of this thread. Please @ mention me in reply and I'll respond quicker. Does the solution from v-xiaotang work for you? I see v-xuding-msft has marked it as solved, but not sure if you're still needing help??

The measure you have tried (below) depends on the purchase time having one value only and depends on if there is a relationship between the two tables already? They shouldn't be related, and your visual you're using it in needs to only have one sales[purchase time], otherwise it will return blank.

price = calculate(selectedvalue(sell price[sell price]),

FILTER(sell price,sell price[start time]<=sales[purchase time]

&&sell price[end time]>sales[purchase time]))

[v-xiaotang · Accepted solution]

Hi @asuka 

you can try this in Power Query,

1. create the fuction

(purchasetime) =>
let
Source = Table.SelectRows(#"sell price", each [start time] <= purchasetime and [end time] > purchasetime)
in
Source

2. add the selected rows into your advanced editor,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLUN9U3NFCK1YlWckISMQGLOMNEzPRNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer = _t, #"purchase time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"purchase time", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SelectRows([purchase time])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"sell price"}, {"Custom.sell price"})
in
#"Expanded Custom"

-

If you want to use measure, you can try this,

price =
CALCULATE (
    MIN ( 'sell price'[sell price] ),
    FILTER (
        ALL ( 'sell price' ),
        'sell price'[start time] <= MIN ( sales[purchase time] )
            && 'sell price'[end time] >= MIN ( sales[purchase time] )
    )
)

result

Best Regards,

Community Support Team _Tang

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

[asuka]

Thanks, Allison! Is it possible to solve this problem in power query?