Create column with value based on date range

[Anonymous]

I have looked at many posts but haven't found one similar to mine. Especially since I don't have a start and end date columns. I have two tables with the relationship below.

Table 1          Table 2

Product  * : * Product

What I want is an extra column in table 2 for value. I want it to look and see that the received date is equal to or between the dates in table 1 and apply the value from table one with the associated product. If the product isn’t found in table 1, then the value should be 0.

I appreciate any help.

Table 1

Value	Updated Date	Product
10	7/10/2023	apple
3	7/10/2023	lemon
4	7/10/2023	orange
4	7/10/2023	pineapple
1	7/10/2023	cherry
3	7/10/2023	peach
2	7/18/2023	apple
8	7/18/2023	lemon
6	7/18/2023	strawberry
10	7/18/2023	blue berry
9	7/18/2023	orange
1	7/18/2023	pineapple
8	7/18/2023	peach
7	7/20/2023	lemon
10	7/20/2023	strawberry
6	7/20/2023	blue berry
6	7/20/2023	orange
9	7/20/2023	pineapple
3	7/20/2023	cherry
5	7/20/2023	peach

Table 2

Received Date	Product
7/17/2023	apple
7/10/2023	lemon
7/19/2023	strawberry
7/20/2023	blue berry
7/20/2023	orange
7/18/2023	pineapple
7/15/2023	cherry
7/18/2023	peach

Final Result of table 2

Product	value	Received Date
apple	10	7/17/2023
lemon	3	7/10/2023
strawberry	6	7/19/2023
blue berry	6	7/20/2023
orange	6	7/20/2023
pineapple	1	7/18/2023
cherry	1	7/15/2023
peach	8	7/18/2023

[Vijay_A_Verma]

Use this in a custom column

List.Last(List.Select(List.Zip({Table1[Product], Table1[Updated Date], Table1[Value]}), (x)=>x{0}=[Product] and x{1}<=[Received Date])){2}

Complete test code

TABLE1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdHdCoIwGMbxW4kdC+6j/LiW4cGUlwzWHKuI7r6Y5ebTzoQfuv8ztWaCs4q1teC15FJ9no33lthQaaZALF0XF+UIsgTjzlQkf3GUPilAp5lCeBVP82SmOYpcpfsv7EBSYQNyuwfzHLfDfqs3H+2DDsl74GygANoPxKK0oo0iC7f5jUkEsQ0wtCJnrT3QvlWBZj/jhC+uM4Y3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, #"Updated Date" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Updated Date", type date}, {"Product", type text}})
in
    #"Changed Type"

TABLE2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3NNc3MjAyVtJRSiwoyElVitUBixrARHNSc/PzYKKWMNHikqLE8qTUoqJKqJQRXENSTmmqAnap/KLEvHS4FRYw4YLMvFQUy01hMskZSOYgaUhNTM5Qio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Received Date" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Received Date", type date}, {"Product", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Last(List.Select(List.Zip({Table1[Product], Table1[Updated Date], Table1[Value]}), (x)=>x{0}=[Product] and x{1}<=[Received Date])){2})
in
    #"Added Custom"

[Anonymous]

Hi @Vijay_A_Verma 

I am working on your solution. I am adding the custom column to table 2 and when I try to put in the values (product, updated date, value) from table 1, they aren't there in the drop down. Only the values from table 2 are available to choose from. Where you have table1 in your code, I was able to select my table 1. 

Do you have any advice on where I went wrong? Thanks!

[Vijay_A_Verma]

In a table, only the columns from that table would be available to choose from. If you refer to another table, you will have to manually type the column name qualified with its table name. That is why I am using Table1[Product] where Product is column name which resides in Table1. When I am putting only [Product], it will refer to Product column of Table2 but Table1[Product] will refer to Product column of Table1. Hence, you will need the paste below in a custom column of Table2.

List.Last(List.Select(List.Zip({Table1[Product], Table1[Updated Date], Table1[Value]}), (x)=>x{0}=[Product] and x{1}<=[Received Date])){2}