Key date to date

[jutu]

Hi. I have say datekey 20241105 but need to convert it to date 2024/11/05 - datekey I have converted it to text format instead of number format

I've tried the below but it gives me error that it cannot be converted to text. Can it be also done with concatenate? Thanks

= Table.AddColumn(#"Changed Type", "FIRST_BILLED_DATE.DATE", each Text.Middle([FIRST_BILLED_DATE], 6, 2) & "/" & Text.Middle([FIRST_BILLED_DATE], 4, 2) & "/" & Text.Start([FIRST_BILLED_DATE], 4))

 

[p45cal]

Try:

= Table.TransformColumnTypes(Source, {{"FIRST_BILLED_DATE", type date}}, "en-GB")

becomes:



which is an actual date type field.

If it's really a string type you need then follow that with:

= Table.AddColumn(#"Changed Type with Locale", "Custom", each Date.ToText([FIRST_BILLED_DATE],[Format="yyyy/MM/dd"]))

:




Thinking about it a bit more, if it's complaining that it can't convert to text, that implies that the previous step may have changed it to a date, so try keeping your existing code but removing either the entire previous step (Changed Type), or only the part of it which changes the FIRST_BILLED_DATE field.
I note that your step converts to:
05/11/2024
not
2024/11/05 as you state.

 

[jutu]

Thank you so much I took good note of the above. I used original code below and just changed the source column to text, but yours is much easier actually thank you very much for your time and for explaining it so well
= Text.Middle([column_name], 6, 2) & "/" & Text.Middle([column_nameE], 4, 2) & "/" & Text.Start([column_name], 4)