Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Xaraja
Helper II
Helper II

Null Values Date Data Type

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?

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1649309484175.png

 

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.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1649309484175.png

 

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.

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.

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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.