Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 ?

Capture.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

11 REPLIES 11
Jimmy801
Community Champion
Community Champion

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
Not applicable

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

 

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
Not applicable

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

 

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
Not applicable

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

Capture.JPG

 

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

Anonymous
Not applicable

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

Hello @Anonymous 

 

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

 

BR

 

Jimmy

Anonymous
Not applicable

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
Not applicable

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
Super User
Super User

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

latestdate.png

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors