Latest date conditional column

[Anonymous]

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 ?

[Jimmy801]

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]

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]

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]

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]

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]

That is right, @Jimmy801 , this part of mine is:

Table details exported to excel: https://drive.google.com/file/d/1D7_K9qVxRgMMWiFbq-n2pol1nJ1koXKe/view?usp=sharing

[Anonymous]

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

[Jimmy801]

Hello @Anonymous 

as @Anonymous already wrote, you have to filter the table on cnt=4 and then selecting latest date. 

BR

Jimmy

[Anonymous]

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 · Accepted solution]

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]

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.