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

11 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super 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.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    here some slightly easier approach

    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 if [Report Refresh Date] = List.Max(#"Changed Type"[Report Refresh Date])  and [cnt] = 4 then 1 else 0)
in
    #"Added Custom"

     

    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

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