Null Values Date Data Type

[Xaraja]

I am working on a dataset I inherited and I am seeing an issue that I don't know exactly how to fix. The underlying SQL table is a list of warehouse events (picking, packing, etc), and there is a column of codes indicating what type of event the row is, as well as a date and time when the event happened. In Power Query, then, the previous designer has added a series of date columns based on the codes in the event type column, so if the event is picking, then put the transaction date in the Picking Date column, otherwise put null in the Picking Date column. Next, if the code is for packing, put the transaction date in the Packing Date column, otherwise null in the Packing Date column, and so on. 

As you can see, there are going to be a ton of null values in the date columns using this method. The report it is used for provides metrics for the warehouse and the employees on how the statistics of various warehouse tasks. I am seeing long tables of "Query Errors" when I go into Power Query for this dataset, but the report seems to be working. Is there an easy way to tell Power BI that it's ok for the date columns to have null or blank values? I can't put bogus dates in those columns for obvious reasons. Is there a different way this ought to be designed?

[v-jingzhang · Accepted solution]

Hi @Xaraja 

Power BI can accept null or blank values in a date data type column. In Power Query Editor, it displays null. In Power BI Desktop, it displays blank. In below image, it shows the same table in two places. 

What is the error message do you see? If the report is working, perhaps the cause is not the null values in date columns. Can you please show the error message after removing sensitive info? 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ShinyIon]

Power bi does not accept null date values in table visual. I just tested it with and without nulls and it fails on nulls.

[Xaraja]

I went on vacation last week, and today, looking at the dataset in Power Query and looking at the error to try to update this thread, I realized there was a very simple error in the formula. There was a column name that wasn't right and so it was not the null values that were erroring out, but the ones that had a date that were throwing errors. The formula looked like this: = Table.AddColumn(#"Added Custom", "Shipped Date", each if [trans_type] = "SH" then [Transaction Date] else if [trans_type] = "SO" then [Transaction Date] else null). The column name was supposed to be [trans_date]. I fixed this and the errors went away. 

Sorry to waste your time with such a simple problem! Clearly I needed that vacation badly. I'm glad to know I can have null values in a date column though.

[v-jingzhang]

Hi @Xaraja 

Glad to hear that you have fixed the error. You can accept an appropriate reply as Solution to help close this thread. Thanks. 

Best regards,

Jing

[lbendlin]

You can change the formulas so they don't produce errors (preferred - use try ... otherwise ...) or you can mass replace all errors with nulls.