Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a column titled "Approval Date" which contains both date (dd/MM/yyyy) and non-date (N/A, To Be Confirmed) fields.
I'd like to convert this column to data type "Date" in order to use associated power BI features in my visualisation like Date Hierarchy, calendar chart plugins, etc.
Problem is, these N/A and TBC fields give an error when converting as they're not date types, and using the Replace Error function only allows me to replace them with null or a date formatted value. Both of these options aren't ideal as they lose the ability to distinguish between N/A and TBC, and I'd prefer a more elegant, perhaps formula-based, solution than say replacing N/A with 1/1/1900 and TBC with 1/1/2999 which clutters the real data quite a bit. Thanks for your help and time in advance!
Solved! Go to Solution.
Hi @Anonymous ,
The calculation in Power BI is for columns. It is not supported to use two or more data type in one column. So you only to change them to null or other date to use the default date hierarchy.
Or you could create a new date column as hierarchy slicer like below:
Date =
IF( 'Table'[Column1] IN {"N/A","TBC"},BLANK(),'Table'[Column1])
It will return the text value.Then change it to date type.
If you want to use the hierarchy that contains "N/A" and "BTC", you need to create the year column and month column etc. one by one and create a hierarchy manually to use it.
Note: For this case, the blank value can't be removed.
Hi @Anonymous ,
The calculation in Power BI is for columns. It is not supported to use two or more data type in one column. So you only to change them to null or other date to use the default date hierarchy.
Or you could create a new date column as hierarchy slicer like below:
Date =
IF( 'Table'[Column1] IN {"N/A","TBC"},BLANK(),'Table'[Column1])
It will return the text value.Then change it to date type.
If you want to use the hierarchy that contains "N/A" and "BTC", you need to create the year column and month column etc. one by one and create a hierarchy manually to use it.
Note: For this case, the blank value can't be removed.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |