How to Change yyyymmdd to Date Format in Power BI?

I’m sure as an analyst, you may have once been faced with the challenge of some data with a date field in the format ‘YYYYMMDD’ (e.g., 20240306) and want to transform it to a proper date format (e.g., 06–03–2024).

In this tutorial, I will show you how to change yyyymmdd to date format in Power BI using DAX and Power Query Editor.

How to Change yyyymmdd to Date Format in Power BI [Using DAX]

Now, I will tell you how to convert yyyymmdd to date in Power BI using DAX.

Scenario:

Let’s say you work for a logistics company that tracks package delivery dates using a format like “yyyymmdd” (e.g., 20240310 for March 10th, 2024). You want to convert these dates into a standard format to analyze delivery trends easily.

According to this scenario, we have an Excel file named Delivery Data with columns Delivery Date. Check the screenshot below.

power bi yyyymmdd to date

Now we see how to convert. Follow the below steps:

1. Open Power BI Desktop and load data using the Get data option. Then, you can see data in the Data pane.

power bi convert yyyymmdd to date

2. Now Go to Table view -> Under the “Table tools” tab, click New column.

Dax convert yyyymmdd to date in Power BI

3. In the formula bar, put the below DAX expression. Then click the Commit button.

DeliveryDate = DATE(VALUE(LEFT('DeliveryData'[Delivery Date],4)), VALUE(MID('DeliveryData'[Delivery Date],5,2)), VALUE(RIGHT('DeliveryData'[Delivery Date],2)))

Where:

  • DeliveryDate = This is the name we’re giving to the new column that will contain the date format.
  • DATE = This function in DAX constructs a date value from the year, month, and day components.
  • VALUE(LEFT(‘DeliveryData'[Delivery Date],4)) = This part extracts the leftmost 4 characters (representing the year) from the “Delivery Date” column in the “DeliveryData” table and converts them into a numeric value.
  • VALUE(MID(‘DeliveryData'[Delivery Date],5,2)) = This section extracts the characters starting from the 5th position up to 2 characters long (representing the month) from the “Delivery Date” column and converts them into a numeric value.
  • VALUE(RIGHT(‘DeliveryData'[Delivery Date],2)) = Here, it takes the rightmost 2 characters (representing the day) from the “Delivery Date” column and converts them into a numeric value.
power bi convert yyyymm to date

4. You see the DeliveryDate created in the Table view.

dax date format yyyymmdd in Power BI

This way, we can use DAX to convert “yyyymmdd” dates to a standard date format in Power BI. This lets you analyze delivery trends and make data-driven decisions to improve logistics operations.

See also  Power bi Dax Today() Function [With real examples]

Power Query Convert yyyymmdd to Date

This example shows how to convert yyyymmdd to date in Power Query Editor.

1. Under the Home tab, click Transform data.

power query yyyymmdd to date

2. Select the column -> Under the Transform, expand Data Type: Whole Number. Check the screenshot below.

power query date from text yyyymmdd

3. Then, in the dialog box, click Text. If you can click Date/Time, it gives you an error.

change date format in power bi

4. Click the Add new step in the Change Column Type dialog box.

power query format date yyyymmdd

5. Then, under the Transform tab, expand Data Type: Text -> click Date.

power query convert yyyymmdd to date

6. Click the Add new step in the Change Column Type dialog box.

power query format date yyyymmdd

7. Now you can see we successfully converted the standard date format in Power Query Editor.

power query date format yyyymmdd

This tutorial taught us how to convert yyyymmdd to date in Power BI using DAX and change the date format yyyymmdd to date in Power Query Editor.

Additionally, you may like some more Power BI articles:

>