Date type and blank rows

Blank rows that belongs in the same column (employee termination date) as Date type data, but Power BI does not seem to like the blanks in a Date column, so it raises an error because the column type is Date. What to do, given the error?

So the blanks represent employees who have not left the company while the dates represent those who have left.

Have you gone through this example below?

I’m relatively confident that this explains your exact scenario that you’re looking to solve.


1 Like

Thanks, Sam!

The Staff Turnover Calculation video is really great and I will definitely be using that.

I think I should have explained the scenario a bit differently. The error in Power BI occurs immediately after changing a data type and loading the data. Here’s the error message:

DataFormat.Error: We couldn’t parse the input provided as a Date value.
Details: 14/12/2012

I actually found the problem and it was the fact that the data was in UK format not USA format, and the reason Power BI did not pick that up was because the dates in the regular Date column all looked liked USA format since the day was always the 1st of each month for all the rows.

But, when I changed the data type of the Termination Date column (a different column) in the Query Editor, Power BI raised the date “14/12/2012” as an error, not realizing that it was in UK date format.

I did not pick this up right away, but when I saw my data not behaving as expected while creating a report, I started digging to find the problem.

The Solution is to change the Regional Settings / Locale for import to English (United Kingdom).

Thanks, Sam!!

Ok great