Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
The current SQL dataset I am working on has several null or blank entries under one of my date columns as they technically have no set end date for their project completion. I am working on Gannt chart visual. However, since some items are blank or null nothing is rendered for these items on the visual. My intent is to create a new column that will copy the valid values for the end date column, and replace any blanks or nulls with a value set five years out from todays date or Jan 1 of whatever the current year is. I also want to ensure that should a value be added later on in the SQL database that once I refresh my report it will include the new date and not hold on to the previous date stamp. Does anyone know if this is possible or perhaps if there is an easier method of doing this?
Solved! Go to Solution.
It is possible like you said in DAX in the model.
It's also possible in Power Query to replace null with whatever value or add a column to check like in the model with DAX. In Power Query you got GUI for these operations.
The best options is alwyas to push it as back as you can to the the data source, as in do the change there in a view.
But all options are valid and possible.
In dax, you just need to create a new cloumn like:
IF(
isblank('your_table'[your_date_cloumn]),
today(),
'your_table'[your_date_column]
)
It is possible like you said in DAX in the model.
It's also possible in Power Query to replace null with whatever value or add a column to check like in the model with DAX. In Power Query you got GUI for these operations.
The best options is alwyas to push it as back as you can to the the data source, as in do the change there in a view.
But all options are valid and possible.
In dax, you just need to create a new cloumn like:
IF(
isblank('your_table'[your_date_cloumn]),
today(),
'your_table'[your_date_column]
)
This is great thanks for the help . This is really really close. It created a new column no problem. It copied all the good data from the first column over perfectly. It entered a value for any nulls or missing data, awesome. The only thing missing now is that it set all the nulls to today. Do you know what the syntax would be to push it out 5 years or realistically any specified allotment of time? Once again thanks for the help so far I really appreciate it.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |