Text Date to YYYY-MM-DD - Power Query Editor

Question

Hi

Please I have a date in text format and I want to change it to YYYY-MM-DD

Answer 1

Hi, thank you for reaching out. My name is Deeksha and I'm a Microsoft user like yourself and I will try to help you as best as I can today.

Text to Columns: First, select the column that contains your dates. Go to the "Data" tab on the Ribbon. Click "Text to Columns". In the wizard, select "Delimited" and click "Next". Uncheck all delimiters, and click "Next". Select "Date" under Column Data Format, and choose "DMY" from the drop-down menu. Click "Finish".

Change Date Format: Now that Excel recognizes your data as dates, you can easily change the format. Select the column again. Right-click and choose "Format Cells". In the Number tab, under Category, select "Date". In the Type list, scroll down to find the "YYYY-MM-DD" format or enter it manually in the custom format box. Click "OK".

Try these steps and hopefully, it resolves your issue. In case you need further help or assistance, please let us know. You can also contact Microsoft Support if the problem persists.

Best regards Deeksha

Answer 2

Hi

I am this to do in Power Query not in excel

Answer 3

If you're using Excel, select the data table you want to modify. Go to the "Data" tab on the Ribbon. Click on "Get Data", then "From Other Sources", and finally "Blank Query". This will open the Power Query Editor. If you already have a query with the date, just go to the query in the editor.

Load Data: If you haven’t already loaded the data into Power Query, do so now by using "Home" > "New Source" and selecting the appropriate source.

Change Data Type to Date (if it's not already): Locate the column with your dates. Right-click on the header of the date column. Select "Change Type" > "Date". This will convert your text into a date format (if it’s not already in this format).

Transforming Date Format: Right-click the date column again. Select "Transform" > "Date" > "Year", which will extract the year. Do the same for the month and day by selecting "Month" and "Day" respectively.

Recombine Extracted Date Parts: Click on "Add Column" tab in the Power Query Editor. Choose "Custom Column". In the new window, create a formula to combine the extracted year, month, and day. Use the format YYYY-MM-DD. For example, if your original date column was named OriginalDate, your formula might look like:

Text.From([Year]) & "-" & Text.PadStart(Text.From([Month]), 2, "0") & "-" & Text.PadStart(Text.From([Day]), 2, "0")

Name this new column (for instance, FormattedDate) and press "OK".

Removing Intermediate Columns: If you created new columns for Year, Month, and Day, and you don’t need them, right-click on each and select "Remove".

Setting Correct Data Type: Right-click on your newly created FormattedDate column. Select "Change Type" > "Date". Close & Load:

Once you're done with these transformations, go to the "Home" tab. Click on "Close & Load" to load your transformed data back into Excel. This process will change your date from the DD-MM-YYYY format to the YYYY-MM-DD format. The key steps involve breaking down the original date into its components (year, month, day) and then reconstructing it in the desired format.