Forum Discussion

Bi2thelly's avatar
Bi2thelly
Helper II
1 year ago
Solved

Filter a merged table list to another date column

I'm not sure I'm doing this correctly but I merged two tables together but did not expand as I wanted records from one table tied to the main table. I'm trying to filter the records to get the max column value that is less than than a column value from the main table. I was trying to use List.Max and Table.Max but have not been able to get them to work correctly. Any insights on this would be great! What I'm looking for is the MAX GoLive_Actual that is less than the NOTI_START_DT.

List.Max([BridgHistory2][GoLive_Actual], 0,[BridgHistory2][GoLive_Actual] < [NOTI_START_DT])

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Bi2thelly ,
    Based on your description, you can try the following, first, filter the BridgHistory2 table to keep only the rows where GoLive_Actual is less than NOTI_START_DT. Then, find the largest GoLive_Actual date from the filtered table.

    let
    BridgHistory2 = #"BridgHistory2",
    NOTI_START_DT = [NOTI_START_DT],
    FilteredTable = Table.SelectRows(BridgHistory2, each [GoLive_Actual] < NOTI_START_DT),
    MaxGoLiveActual = List.Max(FilteredTable[GoLive_Actual])
in
    MaxGoLiveActual

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Bi2thelly ,
    Based on your description, you can try the following, first, filter the BridgHistory2 table to keep only the rows where GoLive_Actual is less than NOTI_START_DT. Then, find the largest GoLive_Actual date from the filtered table.

    let
    BridgHistory2 = #"BridgHistory2",
    NOTI_START_DT = [NOTI_START_DT],
    FilteredTable = Table.SelectRows(BridgHistory2, each [GoLive_Actual] < NOTI_START_DT),
    MaxGoLiveActual = List.Max(FilteredTable[GoLive_Actual])
in
    MaxGoLiveActual

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Hi Bi2thelly 

    use the below formula (make sure both the columns are in type date)

     

     

    =List.Max(List.Select([BridgHistory2][GoLive_Actual], (x)=> x < [NOTI_START_DT]))

  • Bi2thelly's avatar
    Bi2thelly
    Helper II
    1 year ago

    Thank you both for the reply. Both solutions work however, I went with the one using the variables as I may need to add more critieria. Thanks again!