How to convert text columns to dates in Power Query?

by

Converting your data to different types is a common (and time consuming :-)) task in data transformation. Power Query provides simple methods to handle conversion, enabling simpler and more performant ETL processes for subsequent analysis. In today’s post we will show to extract date values from text or string data.

Converting Text to Date in Power Query


Let’s assume that your source system is storing data representing date values, but in string format. You could potentially manipulate the data in the source, however most probably you don’t have access to your Database team. Hence, we will convert the text values to dates in Power Query.

If your data has a somewhat standard format (yyyymmdd) for example, you could follow the simple three step process below:

  1. Start by loading into Power Query from your database, Excel, CSV, API etc’.
  2. In the Power Query Editor, select the column containing text values that represent your dates.
  3. Right-click on the selected column header and select Change Type > Date. This will convert the text values to date format.
  4. Hit Close to save your transformation steps.

Convert text to data using M Code


Conversely, if your text dates are in a non-standard format, you could use M code to specify how to parse them:

  1. In the Power Query Editor, go to the Add Column tab and select Custom Column.
  2. Now go ahead and write a Custom Formula. In the Custom Column dialog box, enter a formula to parse your text column into dates. For example:
= = Table.AddColumn(#"Previous Step", "ParsedDate", each Date.FromText([OrigDate], [Format="yyyyMMdd", Culture="us-EN"]))

Brief explanation of the variables – modify the variable names to fit your requirements:

  • OrigTextColumn: The name of the column that has the text data you want to convert
  • ParsedDate: The name of the column that hosts the converted data – in this case in format “yyyy-mm-dd”
  • #”Previous Step”: is the previous step in your data transformation – this is the input for your formula.
  1. Save your formula and finally hit Close and Apply.

Note: If you receive the following error: DataFormat.Error: The specified culture is not supported, you will need to adjust your Power BI locale in the Regional Settings. You can find the setting by hitting File, then Options and Settings then Options the Current File and pick the Regional Options tab.