Forum Discussion
Filter out date values before year 2000
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}} )
thanks now is working,
can we apply this to all date columns? or shoud I name it column by column in the code?
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}}
)works for me
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.