Forum Discussion
Replace (Blank) null values in DateTime Column with a String
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
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!!
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.
4 Replies
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!!
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.
That doesn't work as it removes the Date Hierarchy I need for the graph when it moves from Datetime to Text
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.
