Forum Discussion
Solved
Power BI
Hi,
In my model I got column "Date" with two types of dates: "day-month-year" and "month/day/year". What would be the formula to unify the date format?
I know how do do that in SQL (
IF(`date` RLIKE "[0-9]*/[0-9]*/[0-9]*", concat_ws("-", split(`date`, "/")[1], split(`date`, "/")[0] ,split(`date`, "/")[2]), `date`))...any hint how to approach that in P-BI?
Hi Ania,
Add a calculated column with the following code:
if Text.PositionOf([Date], "-") > 1 then Text.Replace ([Date], "-", "/") else ( Text.End (Text.Start([Date],5),2) & "/" & Text.Start ([Date],2) & "/" &Text.End ([Date],4) )
See below the full M code so you can test it out on your computer.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjA0MLpVgdINcIlWuob2Csj8I1QeKaIhTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Added Custom" = Table.AddColumn(Source, "Date.1", each if Text.PositionOf([Date], "-") > 1 then Text.Replace ([Date], "-", "/") else ( Text.End (Text.Start([Date],5),2) & "/" & Text.Start ([Date],2) & "/" &Text.End ([Date],4) )), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date.1", type date}}) in #"Changed Type"Any question please tell me.
Regards,
MFelix
2 Replies
Hi Ania,
Add a calculated column with the following code:
if Text.PositionOf([Date], "-") > 1 then Text.Replace ([Date], "-", "/") else ( Text.End (Text.Start([Date],5),2) & "/" & Text.Start ([Date],2) & "/" &Text.End ([Date],4) )
See below the full M code so you can test it out on your computer.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjA0MLpVgdINcIlWuob2Csj8I1QeKaIhTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Added Custom" = Table.AddColumn(Source, "Date.1", each if Text.PositionOf([Date], "-") > 1 then Text.Replace ([Date], "-", "/") else ( Text.End (Text.Start([Date],5),2) & "/" & Text.Start ([Date],2) & "/" &Text.End ([Date],4) )), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date.1", type date}}) in #"Changed Type"Any question please tell me.
Regards,
MFelix
