Forum Discussion
Solved
Extract date from text - very difficult
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?
- 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
3 Replies
- 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
where "test" is the table name - replace "test" with your table name in the formula
Thanks, that worked. I'm not sure if Power Query is a thing in the online version of PowerBI?
