Forum Discussion
Replace date values before specific date or error value to Null
Solution SageAnonymous
= 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}
)
Thanks,
If I wanna apply this to multiple columns in one code, how to write pls?
e.g. Column A, Column B, Column C
- Khushidesai0109
Skilled Sharer
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!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}
)
)
- manikumar34
Anonymous ,
let
Source = ... , // Your data sourceDateTransformation = 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
TransformedTablelet
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
TransformedTableStill not working well
