Forum Discussion
Filter out date values before year 2000
Hi, How can I filter in Query that if the date values before year 2000 then replace to null value? FYI, sometimes raw data has values with 1900-01-01 which is actually no value so I wanna filte...
Hey there!
To filter out date values before the year 2000 and replace them with null values in Power Query (M Language), follow these steps:
Create a Custom Column
- Click on Add Column → Custom Column.
- Enter the following M Code:
NewDateColumn = Table.AddColumn( YourTable, "Filtered Date", each if [YourDateColumn] < #date(2000, 1, 1) then null else [YourDateColumn], type nullable date )Replace the Old Column (Optional)
- If you want to replace the existing date column, modify it directly:
YourTable = Table.TransformColumns(
YourTable,
{{"YourDateColumn", each if _ < #date(2000, 1, 1) then null else _, type nullable date}}
)Hope this helps!
😁😁
You need to change the first parameter to the name of the previous step, looks like should be "Source" in your case
Table.TransformColumns( #"Source", {{"준공예정일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}} )You need to specify each column but can be in the same operation
= Table.TransformColumns( #"TOSS", {{"준공예정일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}, {"Column1", each if _ < #date(2000, 1, 1) then null else _, type nullable date}} )
do you know reason I cannot filter in order?
= Table.TransformColumns(
#"Sorted Rows1",
{{"준공신고서제출일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}}
)