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