Lookup Value in another table based on valid date

The following gives your desired output from your above sorted input:

If your data is not sorted by project_id and activated_on, some changes will need to be made

Note that I have renamed your two data tables as you cannot have two tables with the same name in Power Query (or Excel).

 

let
    Source = Excel.CurrentWorkbook(){[Name="XLS_Timesheet_1"]}[Content],
    #"XLS_1" = Table.TransformColumnTypes(Source,{
        {"id", Int64.Type}, {"project_id", Int64.Type}, {"date", type date}, {"state", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="XLS_Timesheet_2"]}[Content],
   #"XLS_2" = Table.TransformColumnTypes(Source2,{
        {"id", Int64.Type}, {"project_id", Int64.Type}, {"amount", Int64.Type}, {"currency", type text}, 
        {"activated_on", type date}, {"payable_type", type text}}),

    #"Joined XLS" = Table.NestedJoin(#"XLS_1","project_id",#"XLS_2","project_id","Joined XLS",JoinKind.LeftOuter),
    #"Add Rates" = Table.AddColumn(#"Joined XLS","Rates", (r)=>
        let 
            active = Table.SelectRows(r[Joined XLS], each [activated_on] < r[date]),
            client = Table.SelectRows(active, each Text.EndsWith([payable_type],"Client"))[amount],
            contractor = Table.SelectRows(active, each Text.EndsWith([payable_type],"Contractor"))[amount]
        in 
            Record.FromList({List.Last(client), List.Last(contractor)},{"Rate Client", "Rate Contractor"}),
            type [Rate Client=Int64.Type, Rate Contractor=Int64.Type]
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Add Rates",{"Joined XLS"}),
    #"Expanded Rates" = Table.ExpandRecordColumn(#"Removed Columns", "Rates", {"Rate Client", "Rate Contractor"}, {"Rate Client", "Rate Contractor"})
                                                

in
    #"Expanded Rates"

 

 

 

Hi SteveDesmedt, similar solution:

 

Result:

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWB9A2N9IwNDMyDbMSAgyD/M1UUpVidayQihwASrAmOEAlOsCkwQCsywKjAFChjhc4MZQgF2N5gjFGB3gwVCARY3xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, project_id = _t, date = _t, state = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdC7CsMgFIDhd3EOeI7GS8dS8gKFTpqhlA6BYkJwyds3igENtDooCv58qjEESRcHA9jn4XEPa06BUQYo9811WawdnJ/8Zu3tM72dJ2NnCEshiqZwdn59vvy8xpinmJ9UXlP747qiKSxVkeI+U1FR6GuqPK57Un+EparC4agWaPWDdepkZurGl15SqzOTAoZU/SERUofQEmbm+AU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, project_id = _t, amount = _t, currency = _t, activated_on = _t, payable_type = _t]),
    ChangedTypeTable1 = Table.TransformColumnTypes(Table1,{{"date", type date}, {"project_id", Int64.Type}, {"id", Int64.Type}}, "sk-SK"),
    ChangedTypeTable2 = Table.TransformColumnTypes(Table2,{{"id", Int64.Type}, {"project_id", Int64.Type}, {"amount", type number}, {"activated_on", type date}}, "sk-SK"),
    MergedQueries = Table.NestedJoin(ChangedTypeTable1, {"project_id"}, ChangedTypeTable2, {"project_id"}, "Table2", JoinKind.LeftOuter),
    Ad_RateClient = Table.AddColumn(MergedQueries, "Rate Client", each Table.Last(Table.SelectRows([Table2], (x)=> Text.EndsWith(x[payable_type], "Client") and x[activated_on] <= [date]))[amount]?, type number),
    Ad_RateContractor = Table.AddColumn(Ad_RateClient, "Rate Contractor", each Table.Last(Table.SelectRows([Table2], (x)=> Text.EndsWith(x[payable_type], "Contractor") and x[activated_on] <= [date]))[amount]?, type number),
    RemovedColumns = Table.RemoveColumns(Ad_RateContractor,{"Table2"})
in
    RemovedColumns

Hi,

Thanks for the solution dufoq3 and ronrsnfld offered, and i want to offer some more information for user to refer to.

hello SteveDesmedt , i named the tables table1 and table2, you can refer to the following code in table1.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgWB9A2N9IwNDMyDbMSAgyD/M1UUpVidayQihwASrAmOEAlOsCkwQCsywKjAFChjhc4MZQgF2N5gjFGB3gwVCARY3xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, project_id = _t, date = _t, state = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"id", Int64.Type}, {"project_id", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rate Client#(tab)", each let a=[project_id],
b=[date],
c=List.Max(Table.SelectRows(#"Table 2",each [project_id]=a and [activated_on]<=b and [payable_type]="App\Entity\Client")[activated_on])
in Table.SelectRows(#"Table 2",each [project_id]=a and [activated_on]=c and [payable_type]="App\Entity\Client")[amount]{0}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Rate Contractor", each let a=[project_id],
b=[date],
c=List.Max(Table.SelectRows(#"Table 2",each [project_id]=a and [activated_on]<=b and [payable_type]="App\Entity\Contractor")[activated_on])
in try Table.SelectRows(#"Table 2",each [project_id]=a and [activated_on]=c and [payable_type]="App\Entity\Contractor")[amount]{0} otherwise null)
in
    #"Added Custom1"

Output

Best Regards!

Yolo Zhu

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