Hi Aye,

Add a column that extracts the text and ignores numbers. You can use the Column from examples to extract the text values. When you give it the example for the date, enter 'null'. You can then use the fill down.

Mynda

Thanks, Mynda.

can you please let me know the formula of the column for extract the text only.

I am sorry, if you can refer me the course, that will be great too.

Thank you.

regards,

Aye

Use the 'Column from examples' button. It writes the formula for you. Session 4.20.

Hi Mynda,

Thanks for your reply.

I cannot have that written by Column from examples.

Please see attached. I might have done something wrong.

Regards,

Aye

You can do a transformation in that column, check if that value is a number and replace it with null, this way fill down will work in that column.

Value.Is(_, type number) will return true for numbers.

TransformColumn= Table.TransformColumns(#"Changed Type", {{"Period End", each if Value.Is(_, type number) then null else _, type text}})

Fill Down and that's all.

Hi Aye,

In the second example cell type null over the top of the faded 43739 value and it will fill down correctly. i.e. you need to give it the first two examples.

Mynda

Hi Mynda,

Thanks for your reply.

I tried typing up to 11 rows and still recognising. Please see attached.

Please have a look and let me know.

thank you

Regards,

Aye

Then use Catalin's suggestion and add a conditional column with a logical test for a number:

= if Value.Is([Period End], type number) then null else [Period End]

Then you can fill down.

Mynda

Thanks, Mynda,

It works. I did with '_' before and it was not working.

Thanks, Catalin.

Regards,

Aye