Lookup Value in another table based on valid date

[SteveDesmedt]

Hi all, 

I'm struggling with the following problem. 

I have two tables :

1) XLS_timesheet

id	project_id	date	state
1	1	1/03/2016	APPROVED
2	1	1/04/2016	APPROVED
3	1	1/05/2016	APPROVED
4	1	1/06/2016	APPROVED
5	2	1/03/2016	APPROVED
6	2	1/04/2016	APPROVED
7	2	1/05/2016	APPROVED
8	2	1/06/2016	APPROVED

2) XLS_timesheet

id	project_id	amount	currency	activated_on	payable_type
1	1	200	EUR	23/02/2016	App\Entity\Client
2	1	150	EUR	23/02/2016	App\Entity\Contractor
3	1	300	EUR	23/03/2016	App\Entity\Client
4	1	250	EUR	23/03/2016	App\Entity\Contractor
5	1	400	EUR	17/04/2016	App\Entity\Client
6	1	350	EUR	23/04/2016	App\Entity\Contractor
7	2	40	EUR	17/02/2016	App\Entity\Client
8	2	60	EUR	28/03/2016	App\Entity\Contractor
9	2	80	EUR	1/01/2017	App\Entity\Client
10	2	100	EUR	1/01/2017	App\Entity\Contractor

What i want to achieve is adding two additional columns in the table XLS_timesheet to retrieve the correct amount.  (link bewteen two tables is project_id.  The difficulty is all about the date in table XLS_timesheet versus the activated_on field in table XLS_timesheet.
In short only the amount which is activated may be retrieved?

Expected result : 

id	project_id	date	state	Rate Client	Rate Contractor
1	1	1/03/2016	APPROVED	200	150
2	1	1/04/2016	APPROVED	300	250
3	1	1/05/2016	APPROVED	400	350
4	1	1/06/2016	APPROVED	400	350
5	2	1/03/2016	APPROVED	40	0
6	2	1/04/2016	APPROVED	40	60
7	2	1/05/2016	APPROVED	40	60
8	2	1/06/2016	APPROVED	40	60

Where first new column is based on record of payable_type = App\Entity\Client and the scond column App\Entity\Contractor

Can someone help me out to achieve this in Power Query ?

Thanks a lot

[ronrsnfld · Accepted solution]

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"

[dufoq3 · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[dufoq3 · Accepted solution]

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

[ronrsnfld · Accepted solution]

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"