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...
Need Help
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}