Forum Discussion
Extracting specific values from a column
Did you ensure you were replacing the other step, and not creating a new one?
Otherwise, you could try adding a Custom Column in the add column tab, and use the code:
if
Text.Contains([Column1] ,"-") or Text.Contains([Column1] ,"/") or Text.Contains([Column1] ,"\")
then
try
Date.FromText([Column1])
otherwise
null
else
nullChange the red to your column.
It first looks for any of - / \ and if it finds them, tries to transform it to a date
Duplaicate the column by Right click > duplicate column.
Click on the new column, and change the data type of your new column to Date in Transform > Data Type:
Right click on the nbew column and Replace Errors. Type in the word null.
Now your new column will only contain the dates !
Hello, thank you for the reply! This seems to work for the values that are words (such as "Units" in the screenshot of the column), but for values that are numbers (such as "5800" in the screenshot) they are turned into date values rather than errors. As a result, they do not get changed to null when errors are replaced. Could I be missing something here?
Dates are stored as integers, so that would make sense - Try to change to "Date/Time/Timezone" instead of just date
Unfortunately this resulted in the same output, where the numbers changed to date/time/timezone rather than error.
Did you ensure you were replacing the other step, and not creating a new one?
Otherwise, you could try adding a Custom Column in the add column tab, and use the code:
if
Text.Contains([Column1] ,"-") or Text.Contains([Column1] ,"/") or Text.Contains([Column1] ,"\")
then
try
Date.FromText([Column1])
otherwise
null
else
nullChange the red to your column.
It first looks for any of - / \ and if it finds them, tries to transform it to a date