Replace date values before specific date or error value to Null

[jeongkim]

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. 

also 

1. if value null then null.

2. if value before year 2000 then null.

3. if value is not Date format(e.g. just text input by mistake) then null.

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

But hope not to create another column. 

[manikumar34 · Accepted solution]

let
Source = Table.Combine({#"TOSS 데이터_공사목록 - Previous Backup Data",
#"TOSS 데이터_공사목록 - Current Weekly Data"}),

DateTransformation = each
let temp = try Date.From(_)
in
if _ = null then null
else if temp[HasError] then null
else if temp[Value] < #date(2000, 1, 1) then null
else temp[Value],

ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

TransformedTable = Table.TransformColumns(Source,
List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

[Khushidesai0109 · Accepted solution]

Hii @jeongkim 

The error you're seeing likely comes from some cells in the 준공예정일자 and other date columns containing unexpected data types (e.g., records instead of dates or text).

let
Source = Table.Combine({#"TOSS 데이터_공사목록 - Previous Backup Data", #"TOSS 데이터_공사목록 - Current Weekly Data"}),

// Define the transformation function with better error handling
DateTransformation = each try 
if _ = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_) 
otherwise null, // If an error occurs, set the value to null

// List of columns to transform
ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

// Apply the transformation to multiple columns
TransformedTable = Table.TransformColumns(Source, List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

 If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[manikumar34]

@jeongkim 
= Table.TransformColumns(
YourTable,
{"YourDateColumn", each
if _ = null then null
else if try Date.From(_) = null then null
else if Date.Year(Date.From(_)) < 2000 then null
else Date.From(_),
type nullable date}
)

[jeongkim]

Thanks,

If I wanna apply this to multiple columns in one code, how to write pls? 

e.g. Column A, Column B, Column C

[manikumar34]

@jeongkim ,

let
Source = ... , // Your data source

DateTransformation = each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_),

ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

TransformedTable = Table.TransformColumns(Source, List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

[jeongkim]

let
Source = Table.Combine({#"TOSS 데이터_공사목록 - Previous Backup Data", #"TOSS 데이터_공사목록 - Current Weekly Data"}),
DateTransformation = each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_),

ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

TransformedTable = Table.TransformColumns(Source, List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

Still not working well

[manikumar34 · Accepted solution]

let
Source = Table.Combine({#"TOSS 데이터_공사목록 - Previous Backup Data",
#"TOSS 데이터_공사목록 - Current Weekly Data"}),

DateTransformation = each
let temp = try Date.From(_)
in
if _ = null then null
else if temp[HasError] then null
else if temp[Value] < #date(2000, 1, 1) then null
else temp[Value],

ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

TransformedTable = Table.TransformColumns(Source,
List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

[jeongkim]

now working well, thanks

[Khushidesai0109]

The error you're seeing likely comes from some cells in the 준공예정일자 and other date columns containing unexpected data types (e.g., records instead of dates or text).

let
Source = Table.Combine({#"TOSS 데이터_공사목록 - Previous Backup Data", #"TOSS 데이터_공사목록 - Current Weekly Data"}),

// Define the transformation function with better error handling
DateTransformation = each try 
if _ = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_) 
otherwise null, // If an error occurs, set the value to null

// List of columns to transform
ColumnsToTransform = {"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},

// Apply the transformation to multiple columns
TransformedTable = Table.TransformColumns(Source, List.Transform(ColumnsToTransform, each {_, DateTransformation, type nullable date}))

in
TransformedTable

 If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[Khushidesai0109]

Hii @jeongkim 

this might help what you are looking for 

=Table.TransformColumns(
YourTable,
List.Transform({"Column A", "Column B", "Column C"},
each {_, each
if _ = null then null
else if try Date.From(_) = null then null
else if Date.Year(Date.From(_)) < 2000 then null
else Date.From(_),
type nullable date}
)
)



If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[jeongkim]

Got too many errors, pls check. 

just for reference, first one below is I created only for < 2000 date filter and it works well. 

= Table.TransformColumns(
Source,
{{"준공예정일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date},
{"준공신고서제출일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date},
{"준공신고서승인일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date},
{"정산확인일자", each if _ < #date(2000, 1, 1) then null else _, type nullable date}})

This one is the one you adviced for multiple conditions:

= Table.TransformColumns(
Source,
List.Transform({"준공예정일자", "준공신고서제출일자", "준공신고서승인일자", "정산확인일자"},
each {_, each
if _ = null then null
else if try Date.From(_) = null then null
else if Date.Year(Date.From(_)) < 2000 then null
else Date.From(_),
type nullable date}
)
)

[Khushidesai0109]

Hii @jeongkim 
Can you share the error?

Please try this one

= Table.TransformColumns(
Source,
{
{"준공예정일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

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

{"준공신고서승인일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

{"정산확인일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date}
}
)

[jeongkim]

this is the error

[Khushidesai0109]

Try this

= Table.TransformColumns(
Source,
{
{"준공예정일자", each if _ = null then null
else if try Date.From(_) is null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

{"준공신고서제출일자", each if _ = null then null
else if try Date.From(_) is null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

{"준공신고서승인일자", each if _ = null then null
else if try Date.From(_) is null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

{"정산확인일자", each if _ = null then null
else if try Date.From(_) is null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date}
}
)

[Khushidesai0109]

Hii @jeongkim 

Check the updated one Please

= Table.TransformColumns(
Source,
{
{"준공예정일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

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

{"준공신고서승인일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date},

{"정산확인일자", each if _ = null then null
else if try Date.From(_) = null then null
else if Date.From(_) < #date(2000, 1, 1) then null
else Date.From(_), type nullable date}
}
)


If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!

[jeongkim]

still same...