Replace (Blank) null values in DateTime Column with a String

[ElliotG]

Is it possible to replace null/(Blank) row values in a DateTime Column?  I have tried using a power query find and replace but it says it has to be replaced with a DateTime value.  I understand that makes sense so that PBI can categorize the data right but was wondering if there was a way around this.

For example, I have a stacked column chart where the X-axis is grouped by month.  I have a (Blank) bar due to null values in the column.

I'd like to do the following:

1. Replace the (Blank) with a string value "Working"

2. Move that bar to the most right bar in the graph

[sayaliredij · Accepted solution]

Hi @ElliotG ,

I'm uncertain whether it's feasible in Power BI to implement the suggested approach. Since the column currently has only a datatype for datetime, changing it to text might be a workaround, but it raises concerns about losing the benefits of time intelligence and date hierarchy that are provided by a DateTime column.

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[Anuja_Chaudhari]

you can use the M code directly in the Power Query Editor. Here's an example of how to replace null or blank values in a DateTime column named "MyDateTimeColumn" with a specific DateTime value.

[Anonymous · Accepted solution]

Hi @ElliotG

To replace null or blank values in a DateTime column, you need to use a DateTime value consistent with the column rather than a string such as "Working".

This is because Power BI requires consistent data types within columns.

You can consider converting the column data type to text type and replacing the value in power query.

Proceed as follows:

Here is the original dummy data.

Enter power query and modify the data type.

replacement value.

Close & Apply.

If you want to adjust the order of the column, you can modify it in the visual.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ElliotG]

That doesn't work as it removes the Date Hierarchy I need for the graph when it moves from Datetime to Text

[sayaliredij · Accepted solution]

Hi @ElliotG ,

I'm uncertain whether it's feasible in Power BI to implement the suggested approach. Since the column currently has only a datatype for datetime, changing it to text might be a workaround, but it raises concerns about losing the benefits of time intelligence and date hierarchy that are provided by a DateTime column.

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!!