Forum Discussion

JL0101's avatar
JL0101
Helper II
3 years ago

How do I compare a column to a table?

I have below a table with each row has a date in the first column and a table of dates in the second. How do I produce a 3rd column in this table which finds the date in the table that is the closest to the first column (date1) in the custom column?

 

 

Table 

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Please define what you mean by "Closest" .

     

    In the Table.AddColumn function you can use a custom column generator function that  can then implement the logic you define.

     

  • JL0101's avatar
    JL0101
    Helper II
    3 years ago

    Foe example row 1, '17/01/2023' if in the table there was 18/01/2023, 20/01/2023. 25/01/2023. The closest to 17/01/2023 would be 18/01/2023 with only 1 day difference.

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      What if your test value is 19/01/2023 ?  What should the result be?

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQNzDUNzIwMlaK1YlWMjJA5ZoiuLEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}},"es-MX"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Abs(Number.From(#date(2023,1,19)-[Column1])),Int32.Type),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Custom", Order.Ascending}}){0}[Column1]
in
    #"Sorted Rows"