Forum Discussion
Automatically change errors to blanks in date column
In Transform Data i have a text column that is changed to Date type
However, users often type text in the excel column instead of date.
Whenever there is text, i want to replace by null.
Now i have to do it each time manually. I refresh -> replace errors. This is time consuming
Can i do it differently? Can i add a step where PBI replaces text values to null before the step of changing to date type?
Hi Ptolemaida
When you replace errors in Power Query, that becomes part of the transformations and is executed with every refresh so I don't get why you have to manually replace errors each time. Unless there are multiple date columns. I concur to what Ritaf1983 has suggested - data validation should be enforced in the Excel file itself. Additionally, if there are multiple date columns:
= Table.TransformColumns(#"Name of Previous Step", {{"Date1", each try Date.From(_) otherwise null, type date},{"Date2", each try Date.From(_) otherwise null, type date}, {"Date3", each try Date.From(_) otherwise null, type date}})
2 Replies
Hi Ptolemaida
When you replace errors in Power Query, that becomes part of the transformations and is executed with every refresh so I don't get why you have to manually replace errors each time. Unless there are multiple date columns. I concur to what Ritaf1983 has suggested - data validation should be enforced in the Excel file itself. Additionally, if there are multiple date columns:
= Table.TransformColumns(#"Name of Previous Step", {{"Date1", each try Date.From(_) otherwise null, type date},{"Date2", each try Date.From(_) otherwise null, type date}, {"Date3", each try Date.From(_) otherwise null, type date}})Hi Ptolemaida
You can prevent this issue directly in Excel by setting data validation so only date values are allowed (Data Validation → Allow → Date).
Please relate to the linked video:
https://www.youtube.com/watch?v=dGf4N0vak_oThat way, invalid text entries won’t reach Power BI in the first place.
If you prefer to handle it in Power Query, add this step before changing the column type to Date:
= Table.TransformColumns(Source, {{"Date", each try Date.From(_) otherwise null, type date}})
It automatically replaces any non-date text with null, so you don’t need to manually use “Replace Errors” after every refresh.If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
