Power BI convert yyyymmdd to date

SharePoint Online training courses

In this Microsoft Power BI Tutorial, We will discuss how to convert this yyyymmdd format to Date in the Power BI Report.

By taking a simple example, I will show you how you can get the date from the yyyymmdd in the Power BI Report.

Convert yyyymmdd to date in Power BI Report

In this example, I have a SharePoint Online List details name as Budgets. This SharePoint Online Budget list has below columns as:

  • Customer Name (By default it is a Title column)
  • BUD_AMOUNT (Currency Data type)
  • Budget Month (Number Data type)
  • Budget Year (Single line of Text Data type)
  • BUD_DTM (Single line of Text Data type)

You can see the Budgets list in the below screenshot. Here in this list, the BUD_DTM (Single line of text) column is appearing in the below format:

20190105 (YYYYMMDD) where 2019 represents Year, 01 represents Month and 05 represents Day as shown in the below screenshot.

power bi convert yyyymmdd to date

When you will create a report in the Power BI Desktop and add this BUD_DTM column in the X-Axis, then you can see only the number instead of the date format as shown below screenshot.

This is not a perfect date format that you can use in the Power BI Report. You have to make it format as YYYYMMDD. Follow the below process to convert it as a Date format.

power query convert yyyymmdd to date

In the Power BI Desktop page, Go to the Modeling tab and click on the New Column under the Calculations section as like the below screenshot.

power query date format mmm yy

Once you will click on New Column, then a formula bar will appear where you have to apply the below formula to get the exact date format.

DateFormatted = Date(Left([BUD_DTM],4),Right(left([BUD_DTM],6),2),right([BUD_DTM],2))

Here, BUD_DTM is the list column where the value represents as 20190105.

Also, you can follow the below screenshot to add the formula in the formula bar.

power query convert date to month and year

When you applied the above formula in the formula bar, then a new column (Dateformatted) will be added to the Budgets table. Now you have to change the Power BI Column Data type from Text to Date/Time.

To change the column data type, Select the new column (Dateformatted) and go to the Modeling tab. Then select the Data type as Date/Time from the drop-down as you can see in the below screenshot.

convert yyyymmdd to date in power bi

Once you will change the data type as Date/Time, then you can see the date format as shown in the below screenshot.

power query m convert yyyymmdd to date

And also the new column (DateFormatted) will appear in the Date Hierarchy format as like below screenshot.

convert text yyyymmdd to date in Power bi

When you will add this new column (DataFormatted) to the Axis section of any visualization, then you can see only the Year but not the month and date.

To view the proper date format, click on the two-pin icon that is present on the top of the visual. Once you will click on this icon, then you can see all the year, month and date with a proper date format as like below screenshot.

power query yyyymmdd to date

Also, You may like the following Power BI tutorials:

In this Microsoft Power BI Tutorial, We discussed how to convert this yyyymmdd format to Date in the Power BI Report.

By taking a simple example, I have explained to you to get the date from the yyyymmdd in the Power BI Report.

>