Step 1: Import your Data to Power BI
If you haven’t acquired your dataset, make sure to import it into Power BI. In our case we will import an Excel spreadsheet containing the following columns. All entries in the Year column are of type text (strings).
- From any of the Power BI views, hit the Get Data button, pick Excel workbook.
- Then point to your xlsx file on your file system and hit Open.
- Pick the relevant sheets and hit Load.
- Go back to Home and hit Close and Apply.
Step 2: Transform text to Dates with Power Query
Probably the most straightforward way to transform strings to datetime is using Power Query.
- Move to the left hand side Data View.
- Highlight the new table you just loaded (file will be similar to the name of your worksheet) and from the Ribbon hit on Transform Data.
- At the Queries panel to the left highlight your Query.
- Highlight your column.
- Then, from the upper menu hit Transform.
- Change the Data Type to Date.
- You can do the above very easily by hitting the ABC icon next to the column name and select Date.
- Then select Add New Step.
- The column was converted to Date data type.
- Rename the Column if needed. In this case renamed it to Year (Date) as shown below:
Step 3: Extract date from text in Power BI
Second option is to use the Power BI interface to cast the Text column to dates.
- In the Data Pane (at the Right hand side) , search and select your column.
- The Column tools menu will open.
- In the Data Type field in the Ribbon, select Date.
- Then hit Yes.
- Then select the Short Date option in the Format field.
- Your column will be cast to dates.
Note: Pick Date / Time as the format to convert to longer form date times.
Step 4: Use DAX to convert strings to datetime
The last option is to use DAX code.
- In the right hand side data pane, highlight your table.
- Under Table Tools, hit the New column.
- Use the DATEVALUE function that casts strings to datetime in the following manner – make sure to replace the table and column names (in bold) according to your data model.
Date (Date) = DATEVALUE (Interviews[Date (Text)])
Here’s a screenshot: