Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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
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
Proud to be a Super User!
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |