How to convert decimal date to Date type?

[Anmolgan]

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?

[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,

[v-lid-msft · Accepted solution]

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,

[Thim]

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. 🙂

[amitchandak]

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

[Anmolgan]

@amitchandak  Do I need to create custom query for this?

[amitchandak]

Create a new column