Couple of days ago, while working with a simple csv file, i had to cast a column of integers using the YYYYMMDD format to dates, so i can use them in my report. This scenario is pretty common, specifically as typically relational SWL databases store dates as integers.
Acquiring the Dataset
First off, i went ahead and imported the csv file into my Power BI report.
Can’t convert number to dates with DATEVALUE
Then, i went ahead and tried to use the very handy DATEVALUE DAX function to cast the Date_Integer column to a date format.
I switched to the Data View, created a new column and typed the following DAX expression:
Date from Integer = DATEVALUE(Dates[Date_Integer])That failed with the following error message:
Cannot convert value '20220216' of type Text to type Date.
The reason is that DATEVALUE converts text to timestamps and our input here is an integer number column.
Casting number to date time
The solution is to do some DAX blacksmithing to split the integer that has a YYYYMMDD format to a date:
From the Data View, hit New column and type the following expression in your formula to create a new column containing the converted values:
Date from Integer = DATE(LEFT('Dates'[Date_Integer],4),MID('Dates'[Date_Integer],5,2),RIGHT('Dates'[Date_Integer],2))Then highlight the Date from Integer column and change its data type to Date using the Column Tools tab.
The outcome is:
Once done, don’t forget to save your report.
Creating a visual
Switch to your Report View, and create a simple Power BI clustered column plot. In next tutorials i will show how to add labels, modify the titles and legends and additional plot customization techniques.
Follow up learning
How to find if a Power BI column contains one or multiple strings?