Extract date from text - very difficult

[Charlieb]

Hi,

As suggested here, rather than reading from a live .csv to obtain data, I am connected to a folder which will read a dated file, created each day.

Lets assume the filename is 'file_name_2023.08.23.csv'

There is a column on my table called Source.Name. This has this filename inside. I create a new column called Date, inside is this function (assume the 2 numbers match my filename).

Date = DATEVALUE(MID([Source.Name], 18, 10))

I get an error, saying "Cannot convert value '2023.08.23' of type Text to type Date.

This doesn't appear to make a lot of sense?

[Sullyball2a · Accepted solution]

Using the following as a calculated column in Power BI Desktop should work

= DATE(MID(test[Source.Name],24,4),MID(test[Source.Name],21,2),MID(test[Source.Name],18,2))

...but you are much better doing this as an additional column from examples in Power Query

[Charlieb]

Thanks, that worked. I'm not sure if Power Query is a thing in the online version of PowerBI?

[Sullyball2a]

where "test" is the table name - replace "test" with your table name in the formula