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

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

 

Anonymous's avatar
Anonymous
Not applicable
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