Graphed LogoGraphed
Login

How to Convert Text to Date in Power BI

Cody Schneider•

It’s a tale as old as data analysis: you import a dataset into Power BI, ready to create some insightful time-series visuals, only to find your date column is acting like plain old text. Power BI can't create a trend line from "January 1, 2024" if it doesn't recognize it as a date. This article will walk you through several practical methods to convert text into proper, usable dates in Power BI, from one-click fixes to more robust formula-based solutions.

Why Power BI Sometimes Gets Dates Wrong

Before jumping into the fixes, it helps to understand why this happens. Power BI is incredibly smart, but it relies on consistent patterns to recognize data types. When a column of dates gets imported as text, it's usually for one of these reasons:

  • Inconsistent Formatting: Your column might contain a mix of formats like "1/5/2024", "05-Jan-2024", and "2024/01/05". Faced with this inconsistency, Power BI plays it safe and treats the entire column as text.

  • Regional Differences: Is 02/03/2024 February 3rd or March 2nd? It depends on whether your data follows the MM/DD/YYYY format (common in the U.S.) or the DD/MM/YYYY format (common in Europe and elsewhere). If the format doesn't match Power BI's default regional settings (locale), it can lead to misinterpretation or conversion errors.

  • Extra Characters or Spaces: Hidden trailing spaces, non-printing characters, or custom prefixes/suffixes (e.g., "Date: 2024-03-15") can prevent Power BI from recognizing a value as a date.

  • Mixed Data Types: If your date column contains non-date text entries like "N/A", "Pending", or header rows that were accidentally included, Power BI will default the column to a text type to accommodate all the values.

Thankfully, cleaning this up is a standard part of the data preparation process in Power BI, and you have several tools at your disposal.

Method 1: The Quick Fix with "Change Type" in Power Query

For most simple cases, you can handle the conversion right in Power BI's Power Query Editor. This is the simplest and most common approach.

Power Query is the data transformation engine that runs under the hood in Power BI. It’s where you should perform most of your data cleaning tasks, including type conversions.

Step-by-Step Instructions:

  1. From the main Power BI Desktop window, click on Transform data in the Home ribbon. This will open the Power Query Editor.

  2. In the Power Query Editor, find and select the column you want to convert. You'll likely see an "ABC" icon in the column header, indicating it's currently a text data type.

  3. Click on the "ABC" icon directly in the column header. A dropdown menu will appear with a list of data types.

  4. Select Date or Date/Time from the list, depending on your needs.

If your text format is standard and clean (like "YYYY-MM-DD"), Power BI will instantly convert the column, and you'll see the icon change to a calendar. You're all set! But what if you get an error?

Dealing with Ambiguous Dates: "Using Locale"

If Power BI can't parse the date or misinterprets it (like swapping the month and day), you need to give it a hint about the original formatting. This is where "Using Locale" becomes your best friend.

"Locale" is just a fancy term for a region's formatting conventions, including how it writes dates, numbers, and currency.

How to Use It:

  1. Right-click on the column header of your text-formatted date column.

  2. Hover over Change Type and then select Using Locale...

  3. A new window titled "Change Type with Locale" will pop up.

  4. First, set the Data Type to "Date".

  5. Second, and most importantly, select the Locale dropdown. This is where you tell Power BI what format the source data is in. If your dates are in DD/MM/YYYY format, choose a locale where that's standard, like "English (United Kingdom)". If they are MM/DD/YYYY, choose "English (United States)".

  6. Click OK.

By specifying the locale, you’re providing the necessary context for Power BI to correctly interpret otherwise ambiguous dates. This single step solves the vast majority of text-to-date conversion problems.

Method 2: Smart Conversions with "Column From Examples"

Sometimes your date format is truly strange, like "Tue05Mar2024" or "2024_03_05". In these cases, you might struggle with direct conversion. Power BI's "Column From Examples" feature is an intelligent tool that can often figure out the pattern for you.

The idea is simple: you provide one or two examples of the desired output, and Power BI writes the transformation logic for you.

Step-by-Step Instructions:

  1. In the Power Query Editor, select the column containing your non-standard date text.

  2. Go to the Add Column tab in the ribbon.

  3. Click on Column From Examples.

  4. A new, blank column will appear on the right side of your table. In the first row of this new column, start typing the date as you want it to appear. For example, if the original text is "20240315", you might type "03/15/2024". Press Enter.

  5. Depending on the complexity, you may need to provide one more example in the second row. As you type, Power BI analyzes your input and the original column, detects the pattern, and automatically fills in the rest of the new column with its suggested conversions.

  6. If the preview looks correct, click OK. Power BI will create a new column with the cleaned-up date text. It also shows you the M code formula it generated at the top!

  7. Finally, click the "ABC" icon in the header of your newly created column and change the data type to Date. Since you've already standardized the format, this final conversion should be error-free.

Method 3: When to Use DAX Functions (DATE and DATEVALUE)

While Power Query is the preferred place for data shaping, there are times you might need to perform conversions directly within your data model. This typically happens when you’re creating a calculated column in the Data or Report view. For this, you use Data Analysis Expressions (DAX).

Using the DATEVALUE Function

The DATEVALUE function is straightforward. It takes a text string representing a date and converts it into Power BI's native date/time format. However, its major limitation is that it only recognizes standard date formats (like "YYYY/MM/DD", "M/D/YYYY", etc.).

You would create this as a new calculated column.

Example: Imagine you have a table named Sales with a column OrderDateText containing "2024-05-20".

  1. Navigate to the Data view in Power BI Desktop.

  2. Select the Sales table.

  3. From the "Column tools" ribbon, click New Column.

  4. Enter the following formula in the formula bar:

Order Date = DATEVALUE(Sales[OrderDateText])

Press Enter. A new "Order Date" column will appear. Remember to format it as a date using the "Format" dropdown in the Column tools ribbon.

Splitting Text with the DATE, LEFT, MID, and RIGHT Functions

For non-standard text that DATEVALUE can’t read, like "20240315" (a common format from older systems), you need to deconstruct the string and reassemble it with the DATE function. The DATE function requires three arguments: Year, Month, and Day.

We can use text manipulation functions like LEFT (grabs characters from the start), RIGHT (grabs characters from the end), and MID (extracts characters from the middle) to pull the components apart.

Example:

Let's use the same Sales table, but this time OrderDateText contains values like "20240315".

  1. Create a new calculated column.

  2. Enter this formula:

Order Date = VAR YearPart = VALUE(LEFT(Sales[OrderDateText], 4)) // Gets "2024" VAR MonthPart = VALUE(MID(Sales[OrderDateText], 5, 2)) // Gets "03" VAR DayPart = VALUE(RIGHT(Sales[OrderDateText], 2)) // Gets "15" RETURN DATE(YearPart, MonthPart, DayPart)

Here’s a breakdown of the formula:

  • The LEFT, MID, and RIGHT functions extract the text parts for year, month, and day.

  • The VALUE function is used to convert those extracted text parts ("2024", "03", "15") into numbers, as the DATE function requires numeric inputs.

  • Finally, DATE(Year, Month, Day) constructs a proper date from these three numbers.

Troubleshooting Common Errors

Sometimes things just don't work. The most common error you'll see in Power Query is: "We couldn't parse the input provided as a Date value." This means at least one value in your column could not be converted.

Here's how to debug this:

  1. Trim and Clean: Whitespace is a common culprit. Before changing the data type, right-click the column header, select Transform, and then apply both Trim (removes leading/trailing spaces) and Clean (removes non-printing characters). Try the conversion again.

  2. Filter for Errors: Go back to the applied step where a conversion failed. Click on the "[Error]" cells in your data preview. Below the preview, a pane will show the specific reason for the error, pointing you to the problematic value.

  3. Identify Bad Data: Use the filter dropdown on the column header to scan for unusual values. You may find placeholders like "--" or "Not Available" that are causing the entire column to fail. You can use the "Replace Values" feature to change these to null before attempting the type conversion.

Final Thoughts

Converting text to dates in Power BI is a fundamental step in data preparation, but it doesn't have to be a headache. Whether you're using Power Query's intuitive one-click tools and locale settings or applying precise DAX formulas for calculated columns, you can quickly get your data ready for meaningful time-based analysis and visualization.

Fixing data issues like these, one column at a time, is often the most time-consuming part of reporting. At Graphed you can automate this entire process. By integrating directly with data sources like Google Analytics, Shopify, and Salesforce, our platform handles data cleaning and structuring for you. Instead of wrestling with data types, you can simply ask questions in plain English and instantly get back the charts and dashboards you need, updated in real-time.