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

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

Try this code.  Here's a sample PBIX file

 

 

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}}),
    LatestDate = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Report Refresh Date]) in each [Report Refresh Date] = latest)[Report Refresh Date]{0},
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Report Refresh Date] = LatestDate and [cnt] = 4 then 1 else 0)
in
    #"Added Custom"

 

 

Which gives this

Regards

Phil 

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.