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

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.

Method 1: Using Locale

Step 1: Click on Datatype and choose Using Locale.

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.

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

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

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

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.

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.

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.

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.

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)