Forum Discussion

Helper II

9 years ago

Solved

assigning dates based on cell values in a column

HI, I'm wondering how to assign a date in a custom column based on data in another column. I'm new to BI and not able to find a solution. Here's the picture. The data repeats on the first row ...

Tips and Tricks

v-qiuyu-msft
9 years ago
Hi drallam,

You can open Query Editor, add a custom column like below. It will return errors if the value is not a valid date.

Then you can use Replace errors feature to replace errors as null. Utilize Fill-> Down feature to fill null values.

Best Regards,
QiuyunYu

assigning dates based on cell values in a column.zip25 KB

drallam

Helper II

9 years ago

6/12/2017	6/12/2017
6/12/2017	124
6/12/2017	437
6/12/2017	443
6/12/2017	306
6/12/2017	338
6/12/2017	354
6/12/2017	143
6/12/2017	418
6/12/2017	399
6/12/2017	465
6/12/2017	238
6/13/2017	6/13/2017
6/13/2017	299
6/13/2017	399
6/13/2017	100
6/13/2017	492
6/13/2017	498
6/14/2017	6/14/2017
6/14/2017	232
6/14/2017	416
6/14/2017	180
6/14/2017	307
6/14/2017	378

So the original data set is Column B. The intent is to create a custom column A and somehow apply a formula. First step is to referecne only the date cells and paste them in column A. Second Step is Goto>Special>BlankCells> then the cells get selected and then click uparrow and then ctrl+enter. It reference the above cell to the blank ones. How can we get this logic here.?

v-qiuyu-msft