Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Create column with value based on date range

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

ValueUpdated Date

 

Product

107/10/2023apple
37/10/2023lemon
47/10/2023orange
47/10/2023pineapple
17/10/2023cherry
37/10/2023peach
27/18/2023apple
87/18/2023lemon
67/18/2023strawberry
107/18/2023blue berry
97/18/2023orange
17/18/2023pineapple
87/18/2023peach
77/20/2023lemon
107/20/2023strawberry
67/20/2023blue berry
67/20/2023orange
97/20/2023pineapple
37/20/2023cherry
57/20/2023peach

 

Table 2

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

 

Final Result of table 2

Product valueReceived Date
apple107/17/2023
lemon37/10/2023
strawberry67/19/2023
blue berry67/20/2023
orange67/20/2023
pineapple17/18/2023
cherry17/15/2023
peach87/18/2023
Need Help

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        2 years ago

        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}