Forum Discussion
Post ProdigyHow to convert decimal date to Date type?
I want to convert all the dates that are in format of 01.10.2019 to 1/10/2019, like this I tried directly using the Date converter in the Power Query but it does not work and there are many date format that do not changed when I try to applied the date type. Is there any best way to do this, I want to calucalte sales happend on a period lets say 01.01.2019 -1 year, all the sales happend at that period of time, but my dates are in decimal what is the best way to achive this?
Hi Anmolgan ,
If you want to convert "dd.mm.yyyy" as the date format, we can just add "de-AT"or some other culture code in the Table.TransformColumnType function like following:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzTQMzIwtFTSUTJUitWJVgLyDWEiRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}},"de-AT") in #"Changed Type"
Best regards,
6 Replies
- amitchandak
Super User
Try
Combine = mid(DD__MM__YY[Date],4,2) &"/"& left(DD__MM__YY[Date],2) & "/" & right(DD__MM__YY[Date],4) & " "& DD__MM__YY[ time]Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Anmolganamitchandak Do I need to create custom query for this?
- amitchandak
Create a new column
ThimResolver V
Have you tried to use "replace value" under Quiry editor?
If not
go under Quiry editer.
Right click on the date column
choose Replace value
Then replace . with /
If this dosn't work, then try and replace with -
Hope this will help. 🙂
v-lid-msftCommunity Support
Hi Anmolgan ,
If you want to convert "dd.mm.yyyy" as the date format, we can just add "de-AT"or some other culture code in the Table.TransformColumnType function like following:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzTQMzIwtFTSUTJUitWJVgLyDWEiRkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}},"de-AT") in #"Changed Type"
Best regards,- v-lid-msft
Hi Anmolgan ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
