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 filter them out as blank.
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}} )
10 Replies
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}} )
Table.ReplaceValue(#"Table",each if Date.Year([Date Schedule Start]) < 2000 then [Date] else false ,null,Replacer.ReplaceValue,{"Date"})