Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
Solved! Go to 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
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
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
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
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...
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
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
@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
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
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
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.
Proud to be a Super User!
Check out the July 2025 Power BI update to learn about new features.