Filter out date values before year 2000

[jeongkim]

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 filter them out as blank. 

[freginier · Accepted solution]

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!

😁😁

[Deku · Accepted solution]

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}}
)

[Deku]

Table.ReplaceValue(#"Table",each if Date.Year([Date Schedule Start]) < 2000 then [Date] else false ,null,Replacer.ReplaceValue,{"Date"})

[freginier · Accepted solution]

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!

😁😁

[jeongkim]

Sorry not working

= Table.TransformColumns(
#"TOSS 데이터_공사목록 - Append",
{{"준공예정일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}}
)

[Deku · Accepted solution]

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}}
)

[jeongkim]

thanks now is working, 

can we apply this to all date columns? or shoud I name it column by column in the code? 

[Deku · Accepted solution]

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}}
)

[jeongkim]

do you know reason I cannot filter in order?

[jeongkim]

= Table.TransformColumns(
#"Sorted Rows1",
{{"준공신고서제출일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}}
)

[Deku]

works for me

[jeongkim]

I think that code got error when initial value is null. 

Can we create 2 conditions:

1. if value null then null.

2. if value before year 2000 then null.

if value doesn't meet above conditions then keep it as it is.