Forum Discussion
Replace date values before specific date or error value to Null
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.
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
TransformedTableHii Anonymous
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
TransformedTableIf this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
15 Replies
Anonymous
= 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}
)Hii Anonymous
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!
Hii Anonymous
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
TransformedTableIf this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!