Fix dataformat.errors in Power Bi with these steps

2 minute read

Home » Fix » Fix dataformat.errors in Power Bi with these steps

While trying to make changes to the Power BI Service database like adding additional tables to the SQL database, Power BI users may encounter various data format errors. Some of the error includes DataFormat.Error: We reached the end of the buffer or power bi dataformat.error external table is not in the expected format.

If you are also troubled by this Power BI errors, here are a couple of troubleshooting tips to resolve the issue for a few Dataformat.errors.

How to fix common dataformat.errors in Power BI

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

Fix dataformat.errors in Power BI

Check the File Size

  1. If the error occurs when you try to import data from multiple files simultaneously then it can be due to issues with the File Size.
  2. Check the json file size to make sure it is not related to your file size.

Wait, Wait and Wait!

  1. If it is a temporary issue then there is no point in trying to troubleshoot the issue out of your comfort zone.
  2. Users have reported that the data format error was resolved automatically after a day or two.
  3. So, check with the Power BI support in case the issue is at their end.

If the issue persists, do the following.

  1. If you are doing PowerQuery, try to abandon it and set up a staging table in SQL database that parses the JSON using T-SQL.

Learn more about adding filters in Power BI by reading this guide.

2. Power BI dataformat.error external table is not in the expected format

Save the File with Excel

  1. If you are trying to use excel file imported from third-party software like accounting software, then it can have minor XML schema errors.
  2. While these errors can be ignored by the Excel app but results in an error when used with Power Query.
  3. One way to resolve this issue is to open the problematic Excel file in Excel app and resave it.
  4. Now import the same file in Power Query and check if the error occurs again.
  5. This can be a time-consuming task if you have many files to work with. However, as a workaround, you can get through the issue until a reliable fix is found.

Change Type in Applied Steps

  1. First import the file you want to use.
  2. In the Navigator window, click on the Edit button beside the Load button.
    Fix dataformat.errors in Power BI
  3. From the right pane, under Applied Steps, delete Change Type step.
    Fix dataformat.errors in Power BI
  4. Click on Close and Apply.

If the issue persists, try removing initial changed of for date data type from number to text.

3.  Power BI dataformat.error invalid cell value #name/ #ref

Fix dataformat.errors in Power BI

  1. Try to correct the error in the Excel app before importing it.
  2. Check if any of the Excel formulae have #N/A value which is the root cause of this issue. Replace the value with Null or space.



Next up

Google Chrome doesn’t finish downloads? Try this

Alexandru Voiculescu By: Alexandru Voiculescu
2 minute read

We all know how good Google Chrome is. This is why the browser became the most popular surfing tool in the world. Usually, Chrome is […]

Continue Reading

Not enough physical memory error in VMware [FULL FIX]

Rabia Noureen avatar. By: Rabia Noureen
2 minute read

A large number of VMware users have reported encountering the following error message: Not enough physical memory is available to power this virtual machine with […]

Continue Reading

Troubles with your Uplay login? Here’s what you can do

John Taylor avatar. By: John Taylor
2 minute read

Some users have reported that they are having problems when trying to log in their Uplay client. Apparently, they receive an error message that looks […]

Continue Reading