Forum Discussion
Help with Syntax to keep rows if date is not modified within 48 hours?
I'm new to Power BI and trying to use the Advanced Editor and add syntax to keep the rows if the last modified date is not within the last 48 hours. Here is my code so far:
let
Source = #"Ticket Data",
#"Null Target Date" = Table.SelectRows(#"Source", each ([Target Date] = null or (DateTime.LocalNow() > [Target Date] ))),
#"Changed Type" = Table.TransformColumnTypes(#"Null Target Date",{{"Submitted Date", type datetime}, {"Last Modified Date", type datetime}, {"Target Date", type datetime}})
in
#"Changed Type"
I have a column named "Last Modified Date" in the format of:
| 4/8/2021 3:42:34 PM |
My table is pulled from a .json from a Sharepoint file which includes tickets in a queue and details about the ticket. How would I add another OR statement to include rows where "Last modified date" is NOT within the last 48 hours?
try this ( I modified your script by adding the expression that checks if [target data] is before the last 48 hours )
let Source = #"Ticket Data", #"Null Target Date" = Table.SelectRows(#"Source", each [Target Date] = null or (not DateTime.IsInPreviousNHours([Target Date],48 ))), #"Changed Type" = Table.TransformColumnTypes(#"Null Target Date",{{"Submitted Date", type datetime}, {"Last Modified Date", type datetime}, {"Target Date", type datetime}}) in #"Changed Type"
3 Replies
Thank you for this! How do I go about getting this configured? Do I need to add this table? How do I then compare my column of "Last Modified Date?"
try this ( I modified your script by adding the expression that checks if [target data] is before the last 48 hours )
let Source = #"Ticket Data", #"Null Target Date" = Table.SelectRows(#"Source", each [Target Date] = null or (not DateTime.IsInPreviousNHours([Target Date],48 ))), #"Changed Type" = Table.TransformColumnTypes(#"Null Target Date",{{"Submitted Date", type datetime}, {"Last Modified Date", type datetime}, {"Target Date", type datetime}}) in #"Changed Type"
