Search code examples
mergepowerbipowerquerydate-range

Merging tables by date range in PowerBI

I have 2 tables:

Table 1: enrollment

student_id  enrollment_date
1000        4/26/2022
1001        11/15/2022
1002        6/6/2022
1003        9/6/2022
1001        9/14/2022
1004        9/21/2022
1002        11/13/2022
1005        12/27/2022

Table 2: promotion

promo_id    promotion             promo_start   promo_end
1           30% Promo             9/5/2022      9/26/2022
2           Christmas Promotion   12/25/2022    12/31/2022
3           11.11 Promotion       11/11/2022    11/15/2022

What I want to achieve is to merge 2 tables together based on date range defined in promotion table. If the enrollment_date did not fall into any promotion period, then just leave it as null. Below is the expected output:

enter image description here

Any advise or help will be greatly appreciated!

Solution

  • Here you go.

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYxBCoAgEEWvIgPtIv0jBq27QHtp0a4WJaj3J0lz/u495o33BFVHI1kzqC2GO6i+YhftNBvmn3j+kPbRE8vZesYr5ftI9UW+wlMsWHPLK1lIbaUGJkDKbjUgdSHX6v0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [promo_id = _t, promotion = _t, promo_start = _t, promo_end = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"promo_id", Int64.Type}, {"promotion", type text}, {"promo_start", type text}, {"promo_end", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"promo_end", type date},{"promo_start", type date}}, "en-US")
in
    #"Changed Type with Locale"

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc7LCcAwDAPQVYrPAVnKp2SW0P3XKA0lrQ/x9SHJYxjd/ZhnyQrUIJfsSpO4iARrMC1riKm8pGPX18ESqPxI3Cw9X+Rg9TNB52vXDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [student_id = _t, enrollment_date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"student_id", Int64.Type}, {"enrollment_date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"enrollment_date", type date}}, "en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "promo_id", (x)=> Table.SelectRows(promotion, (y)=>  x[enrollment_date] >= y[promo_start] and x[enrollment_date] <= y[promo_end]){0}?[promo_id]?)
in
    #"Added Custom"