Skip to main content

News

Errors In Date Column and Methods to Remedy Them in Power BI.

Two programmers working together with lines of code superimposed over them

Hello readers, the errors in the date field can be caused due to many reasons like format issues from the source file itself, the date is some times found in scientific numbers, or sometimes half date is missing. I am presenting you with two common scenarios and methods to remedy them, which I have faced in my recent project in Power Bi. I hope this blog may help to resolve similar issues faced by the end users.

Scenario: The Date Column Format is Unable to Transform to Default Format.

Findings From Date of Birth Column:

  1. Column Date format is in MM-DD-YY
  2. Locale Date by default is English (India) – DD-MM-YY
  3. Power Bi presently considers dates in the format DD-MM-YY due to default setting, and records are therefore regarded as errors after the data is changed.

Picture1     Picture2

 

Method 1: Using Locale

Step 1: Click on Datatype and choose Using Locale.

Picture3

Step 2: The Change Type by Locale window will appear. Select the data type Date and Locale according to the date format used in the country. In this case, select English (United States) because MM-DD-YY is used in the United States.

Picture4  Picture5

Finally, the dates could be transformed into Date datatype, and the format is modified to MM-DD-YY without any error.

Picture6

 Picture8

 

 

 

Scenario: When the Date Column Contains Special Characters and is Unable to Transform Datatype

Picture9 Picture10

 

Method 2: For this scenario, we will use the Split and Merge Column Method

Step 1: Split the Date column into three columns as we will require DAY, MONTH, and YEAR. Click Split Column from Home Tab and select By delimiter. A window will pop up.

Picture11

Step 2: In Split Column by Delimiter window, select delimiter as the Custom and enter the unique character. In this case, we will split the column two times, once by Point and second by using Space as the delimiter.

Picture12

 

Picture13

Step 3: Select Columns from Examples from the Add Column tab. It will guide you through the process of entering sample values to create a new column. A drop-down menu will appear when you double-click on the first row of the new column (Date). Select 01-01-1962 (Date From year) as the datatype for which the new column should be created. Please keep in mind that all columns must be selected.

Picture14

 

Picture15

Step 4: Starting with the first row in the new column, enter the correct dates in the appropriate rows until the valid values ​​are auto-generated. The date format corresponds to DD-MM-YYYY in default mode.

Step 5: Click on Ok, and later you can remove the Month, Day, Year column but keep the New Column (Date) for report use.

Picture16  Picture18

Takeaways:

  1. Types of Error
  2. Two Methods to Deal with errors
  3. Using Locale (Right Click on Datatype)
  4. Split Column (Home tab)
  5. Column From Example (Add Column tab)

Thoughts on “Errors In Date Column and Methods to Remedy Them in Power BI.”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Akshay Lichade

Akshay Lichade is an Associate Technical Consultant at Perficient. He is a Power BI Developer with over 3 years of experience.

More from this Author

Categories
Follow Us