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

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...

 

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

did you change the variable 

DefineDateInterval

 accordingly? I inputed there 0, -2, and -4 to work with the example. You have to adapt that to your needs

 

Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jimmy801, I think why the code did work on the first table is that coincedentally latest date had cnt=4 and it does not work on the other scenario where latest date cnt=3. Somehow I think I need first filter cnt and then select the latest date. But if I do it simply filtering column cnt=4, I lose required dates.

      Not sure whether I was clear enough.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

         

        Just change code a little bit in this line:

        LatestDate = List.Max(#"Changed Type"[Report Refresh Date]),

         

        to 

        LatestDate = List.Max(Table.SelectRows(#"Changed Type", each [cnt] = 4)[Report Refresh Date]),

        Kind regards,

        JB