Forum Discussion
Solved
Cleaning Data, Fill Down
I'd kindly like to ask how to remove the dates from the this column so that they are blank and then replace the blank cells with the region by filling down. So basically the dates should be replaced ...
Hi HamidBee ,
Please try the following formula to directly tranform the column instead of adding a new custom column:
#"Transformed Column"= Table.ReplaceValue(Table.TransformColumns(#"Changed Type",{"Column1",each Text.Remove(_,{"0".."9"})}) ,"//",null,Replacer.ReplaceValue,{"Column1"} ),Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
How I would do it
1) make an extra helping column, depicting the rows with dates:
Add conditional column (Helpcolumn) > if column1 contains "/" Then null Else OK2) add an extra column with the correct contents
Add Conditional column (Cleaned): if Helpcolumn equals OK Then Column1 Else HelpcolumnWith the last statement you just grabbed the normal information from your first initial column (Aberdeen etc) and gave null (blank values) when it's a date.
Now, if you select the output column (Cleaned) and then hit Fill > Down, it should do exactly what you requested.
to
Cheers
