Resolving Dataformat Errors in Power Query M

DataFormat errors in Power Query can disrupt the flow of data transformation. These errors usually indicate a mismatch between the expected format of a value and the actual format provided by the input.

Understanding how to correct these errors is helpful when working with your queries. Let’s dive into what causes these errors and how to fix them.

What Causes DataFormat Errors?

At the core of DataFormat errors lies a mismatch in expected formats. Here’s what typically triggers these errors:

1. Format Mismatches
   Imagine telling Power Query to treat the word “Apple” as a date. It wouldn’t make sense. This is what happens when there’s a date format mismatch. The input must follow a recognized date pattern to be converted.
2. Numeric Conversion Failure
   Similarly, Power Query can stumble when asked to view a text like “Banana” as a number. Without a proper numeric format, this conversion will fail.
3. Unexpected Error Values from Excel
   When importing Excel data, encountering cells with errors such as #DIV/0! or #NUM! can be the reason for an error. Power Query expects standard values, not indicators of problems in the Excel sheet.
4. Empty or Incorrect File Types
   Feeding Power Query an empty string when it’s expecting a JSON document, or giving it a CSV file when it expects an Excel file, are ways to encounter a DataFormat error.

Examples of DataFormat Errors

DataFormat.Error: We couldn’t parse the input provided

This error is encountered when Power Query cannot interpret the provided input within the expected format. For instance, attempting to convert non-date text to a date using the Date.From function will result in an error.

Date.From("Orange") // Results in an error due to format mismatch.

DataFormat.Error: We couldn’t convert to

You can also get an error message when the engine tries to convert a value into a value of a different kind but can’t. The following example uses Number.From to convert the text “Orange” into a number, leading to a number conversion failure.

Number.From("Orange") // Text-to-number conversion is not possible.

DataFormat.Error: Invalid cell value

Another common error is the invalid cell value error. It can occur when importing error values from an excel file. If your imported data contains any values of:

Description	Format
#DIV/0!	This error emerges if there’s an attempt to divide by zero within a formula.
#NUM!	This occurs when a function’s outcome is either too small or too large to be computed, or if the inputs do not align with the function’s prerequisites. For example, a DATEDIFF function will return this error if the start date is later than the end date.
#N/A	This is seen when a lookup operation, such as VLOOKUP, cannot locate a matching value.
#REF!	This indicates a formula is pointing to a cell that no longer exists.
#VALUE!	This signals that the data type expected by a formula is at odds with the data type actually provided, like when a text value is used in a subtraction operation that anticipates a numeric value.

DataFormat.Error: We reached the end of the buffer.

When your formula receives a value that it doesn’t expect, it can return a data format error. The following examples provide the Json.Document function with an empty string, resulting in an error. This can also happen when the data source is too large, causing memory issues.

Json.Document("") // Fails due to an empty string input.

DataFormat.Error: We found an unexpected character in the input

Similarly, when you try to provide the Json.Document function with a random letter, like “a”, it does not align with a format the function expects. It then leads to a data format error.

Json.Document("a") // Fails because "a" is not valid JSON.

Dataformat.Error: External table is not in the expected format

When connecting to data, there is a wide range of functions available. Some of them expect the source they connect to, to be in a specific format. For example, when connecting to a file using Excel.Workbook, the function requires an Excel file as input. When providing a CSV file, a data format error occurs.

// Fails with a CSV when an Excel file is expected.
Excel.Workbook( File.Contents( "C:\Data\Market Prices.csv" ), null, true)

Conclusion

DataFormat errors in Power Query are often signs that something is not in the right format. Understanding and aligning your data with the required formats can prevent these errors from occurring.

Clean and validate your data, watch for file type requirements, and handle special cases like empty inputs to keep your data transformation process smooth and error-free.

Further Reading:

• Error Messages in Power Query
• Understanding Expression Syntax Errors in Power Query M