If you are working with data that comes from different sources in Power BI, you will eventually face difficulties in date format coming from different systems.

Fortunately, Power BI is equipped with tools that can help you deal with different scenarios, allowing you to transform the date to the proper format.

In this article, you will explore different ways to convert a number to date. You will learn a few tricks that will allow you to deal with most scenarios.

Convert 8 Digit Number to Date

In this scenario, you will have the date as an 8-digit number without a date separator.

For example, 29/10/2023 will be presented as 20231029.

The above is an example of an 8-digit number that is presented as YYYYMMDD, you might face a different date format. But the tools and tricks you will learn will help you deal with any format.

You can deal with this scenario in Power Query. In the following section, you will explore different ways to do this in Power Query, the purpose of seeing different scenarios is to equip you with a number of skills that will allow you to deal with any scenario.

Convert to Date with Power Query Extract and Merge

The principle of this solution is to Extract each part of the 8-digit number and then merge them in one date column.

1. Start with launching the Query Editor, Go to Home and click on the Transform data button. Select the Query in which you want to perform these steps.

2. Select the column which has the 8-digit number and go the Add Column tab.

3. Now to extract the Year part of the date, open the Extract drop down, and select First Characters. Input 4 in the Count field.
4. To extract the Month part of the date, open the Extract drop down, and select Range. Input 4 as the Starting Index and 2 as Number of Characters.
5. Can you guess the next step? Exactly. To extract the Day part, open the Extract drop down, and select Last Characters. Input 2 in the Count field.

Now you have to merge the 3 newly created columns but with adding the date separator.

6. Start by holding down the Ctrl button and select the 3 columns in this order Day, Month, Year.

7. Go to Transform tab and select Merge Columns, choose Custom separator, input a Hyphen “-“. Name the column Date.

8. Finally, you will have to change the data type of the new Date column to Date.

Convert to Date with Power Query Column from Examples

In Power Query, you can create new columns by giving a few example values. You can use your current data or select some columns to guide the process. This is handy when you know what you want in your new column but are not sure which transformations you should use.

1. While you are in the Query Editor, Select the column which has the 8-digit number and go the Add Column tab.
2. Go to the Column From Examples option and choose From Selection. A blank column is added.

3. Start writing the proper date that you want, based on the first cell in your data, in this example it will be (26/10/2023), notice that while you are writing Power Query will start suggesting some values.
4. Choose the suggestion with the date icon. Press Ctrl + Enter to fill all the rows with the same transformation.
5. Review some of the values generated and if you see any problem, go to the cell that needs correction and change it to the correct value. Power Query will change the transformations accordingly for the new column. You can keep doing this till you reach the correct result.
6. Once you are satisfied, select OK.

Did you notice that Power Query was smart enough to name the new column as Date?

Convert to Date with Power Query Text to Date Data Type Change

This method relies on subsequent data type changes. It also works with specific arrangements of the 8-digit number, so It is simple, but might nor be reliable with scenarios different from the YYYYMMDD that we are currently working on.

1. While you are in the Query Editor, Change the Data type of the column which has the 8-digit number to Text format. If you are asked to add or replace the current conversion, select Replace current.

2. Open the Data type drop down menu again but now select Date. This time when you are asked to add or replace the current conversion, select Add new step.
3. Now your column is changed to a proper Date format.

Convert to Date with DAX

You can also solve this problem with a DAX calculated column. You can utilize the Text functions (Left, Mid, Right) to extract the different date parts from the 8-digit number, then convert them to a date using the Date function.

1. In the Table view, go to your Table, select Table tools and select New Column
2. Input the below DAX function, but replace [Column1] with your column.

= DATE(LEFT([Column1],4), MID([Column1],5,2), RIGHT([Column1],2))

Convert Serial Number to Date

In this scenario, Dates are numbers where 1 represents the Date (31 – Dec – 1899)

Each day after this date we add 1, for example, the date (01 – Jan – 2022) is equivalent to the number 44562, because 44,561 days have passed from (31 – Dec – 1899) to (01 – Jan – 2022).

Times are fractions of day, since each day is 24 Hours, the ¼  of one day equal (24/4 = 6), so 6:00 AM is equivalent to the number 0.25.

📝 Note: Power BI and Power Query represents the number 1 as (31 – Dec – 1899) while excel represents the number 1 as (01 – Jan – 1900). They all match starting from (01 – Mar – 1900) which is the number 61. This is because Excel treats the year 1900 as a Leap year.

Now, let’s crack on, you will explore ways to deal with each of those 2 scenarios.

This scenario is simple and can be solved with either Power Query or a simple transformation on the Table view.

Convert to Date with Power Query

While you are in the Query Editor, Change the Data type of the column which has the date value number to Date If you are asked to add or replace the current conversion, select Add new step.

Note: please make sure that the column initial data type is a numerical Data type not a Text data type.

📝 Note: Please make sure that the column’s initial data type is a numerical Data type, not a Text data type.

Convert to Date with DAX

1. In the Table view, go to your Table and select your column.
2. Go to Column tools, change the data type to Date.

Again, you need to make sure that the column data type was initialized a numerical Data type

Conclusions

Power BI’s data transformation capabilities offer solutions for common challenges in standardizing date formats from diverse sources.

This article addresses two scenarios for converting numbers to dates: one involving 8-digit numbers without date separators and the other representing dates as numbers.

For the first scenario, you explored multiple Power Query solutions and a DAX option, giving you the flexibility to choose the method that best suits your needs.

The second scenario, simpler in nature, can be handled with Power Query or a straightforward transformation in the Table view.

These techniques empower you to efficiently manage date formats and ensure they align with your analytical requirements, making Power BI a versatile tool for data transformation and analysis.