Datevalue formula converting into wrong format

[ErikBI]

Hey guys,

I have a data file with horrendous date values. They're exported as "ddd dd-mm-yy" (see "Begindatum")

When imported into PowerBi this is seen as a string.

So I made a custom column:

Start = DATEVALUE(MID([Begindatum],4,99))

But this converts 14/12/20 (meant to be 14 dec 2020) into 12/10/2014 as seen below.

How do I tell the datevalue formula "bruh, you need to do dd-mm-yy"?

My windows date settings are as they should be. 

cheers

  

[PhilipTreacy]

Hi @ErikBI 

Please download this sample PBIX file with data and working code/result

Load your data into the Power Query editor, then split the column by delimiter (space).  Delete the text column and then convert the column containing the date into an actual Date column

Here's the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk1MVDA00TU00jUyUIrViVYqK8pUMDLSNdQ1MkTwzXSNkPkg1TA+qgGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Begindatum = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Begindatum", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Begindatum.1", "Begindatum.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Begindatum.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Begindatum.2", type date}})
in
    #"Changed Type"

Regards

Phil

[smpa01]

@ErikBIcan you try this for a calculated column

Column = 
var strin = TRIM(MID([Column1],4,99))
var year = RIGHT(strin,2)
var month = mid(strin,4,2)
var day = LEFT(strin,2)
var datestring = DATEVALUE(CONCATENATE(year,CONCATENATE("-",CONCATENATE(month,CONCATENATE("-",day)))))
Return datestring

[ErikBI · Accepted solution]

Hey @smpa01. I tried your solution but it didnt quite work. The reason for this is because sometimes a day/month can have just 1 character, meaning that always taking 2 characters wont work.

Based on your solution I worked my way to my own version that seems to work for all date inputs.

So for that reason, thanks for the kickstart to the solution 🙂



I figured it out with the following calculated column:

Start = 
var strin = TRIM(MID([Begindatum],4,99))


var day = LEFT(strin,SEARCH("-",strin,1,0)-1)
var month = ABS(MID(strin,SEARCH("-",strin,1,0)+1,2))
var year = "20"&RIGHT(strin,2)


var dat = DATE(year,month,day)
return dat