Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Latest date conditional column

Guys, I need an extra column in PQ which would check the 'Date' and 'cnt' columns and bring 1 if there is a latest date in 'Date' and 'cnt' =4, else 0   could you please help with syntax ?
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Strictly speaking, as the DefineDate, LatestDate and DefineInterval are not supposed to change for each record, I would move it out of the cycle to save CPU ticks as in the current code they are getting redefined for each row, which may be an issue for extremely large datasets:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvJCQAgDAXRXv5ZMItLMcH+21AiSCTXx4wZZmWuQkIoUKxiGEGaSw8iLi1dmi5JDV95hR6gDyjC2g==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Refresh Date" = _t, cnt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Refresh Date", type date}, {"cnt", Int64.Type}}),
    
    DefineDateInterval = {0, -2, -4, -7},
    LatestDate = List.Max(Table.SelectRows(#"Changed Type", each [cnt] = 4)[Report Refresh Date]),
    DefineDate = List.Transform(DefineDateInterval, each Date.AddDays(LatestDate, _)),

    #"Added Custom" = Table.AddColumn
    (
        #"Changed Type",
        "Custom", 
        each if List.Contains(DefineDate, [Report Refresh Date])  and [cnt] = 4 then 1 else 0
    )
in
    #"Added Custom"

    This also makes the code a bit lighter.

     

    Kind regards,

    JB

Anonymous's avatar
Anonymous
Not applicable
5 years ago

thank, you, Jimmy801 , that worked. Would you happen to know how I can enhance the logic in a way

so Custom brings result 1 not only to this one latest date where cnt=4 but also -7, -14, -21 days from it? the outcome should be 

 

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
5 years ago

Hello Anonymous 

 

check out this solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NNQ3MjAyUNJRMlGK1YlWMkcSMQaLmGGoMUUSMQKLmGDoMsbQZYSqJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Refresh Date" = _t, cnt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Refresh Date", type date}, {"cnt", Int64.Type}}),

    #"Added Custom" = Table.AddColumn
    (
        #"Changed Type",
        "Custom", 
        each 
        let 
            DefineDateInterval = {0, -2, -4},
            LatestDate = List.Max(#"Changed Type"[Report Refresh Date]),
            DefineDate = List.Transform(DefineDateInterval, each Date.AddDays(LatestDate, _)),
            Check =if List.Contains(DefineDate, [Report Refresh Date])  and [cnt] = 4 then 1 else 0
        in 
            Check
    )
in
    #"Added Custom"

Use the variable DefineDateInterval to define de intervals of days to the latest date to check

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    thanks a lot, Jimmy801 , this seems to be working with the example table but I have another table with bit different dates and that does not seem to be working there...