Convert from MMM-YY Format in CSV to DD-MM-YYYY Format in Power BI

[atult]

I have my date column as MMM-YY(Example: JUN-19) in csv file. I want to take it as DD-MM-YYYY(Example: 01-06-2019) format in Power BI.

The date is taken as text column when imported. When I convert the datatype to Date, it takes the YY-DD-CurrentYYYY(Example: 19-06-2021) format which is incorrect.

In CSV	When DT changed to Date in PBI	Expected Output
Jun-19	19-06-2021	01-06-2021
Jul-19	19-07-2021	01-07-2021
Aug-19	19-08-2021	01-08-2021
Sep-19	19-09-2021	01-09-2021

Image Representation of requirement:

Note: Changing the format to long date in CSV file is resolving issue but I do not want to change things at CSV file end since it will be a manual effort every time.

[ERD · Accepted solution]

Hi @atult ,

You can create another column and use the next code:

1. Add Column - Custom Column

2. Type = Date.From("01-" & [In CSV])

= Table.AddColumn(Source, "Custom", each Date.From("01-" & [In CSV]))

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[selimovd]

Hey @atult ,

usually the DATE.FROM function in Power Query should be able to handle that.

Check the following query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8irN0zW0VNJRMrTUNTDTNTIwMgRyDAzhnFgdkKIchCJzZEXmCEWOpekIRRbIiiwQioJTCxCKLJEVQTmxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"n CSV" = _t, #"When DT changed to Date in PBI" = _t, #"Expected Output" = _t]),
    #"Inserted Date" = Table.AddColumn(Source, "Date", each Date.From([n CSV]), type date)
in
    #"Inserted Date"

Let me know if that works for you.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[atult]

Hi @selimovd ,

This is not working.

As I said, when the Date(MMM-YY) is converted to date datatype, it is giving me incorrect date as I shown in the table in my initial query.

Hence, even with DATE.FROM function, I'm getting "19-06-2021" instead of "01-06-2021" for "JUN-19".

[Anonymous]

Hi @atult 

Can you try using the format option available in the data view and change to desired format by typing it in the date format drop down list.

Thanks