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

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.
   
3. From the right pane, under Applied Steps, delete Change Type step.
   
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

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.

RELATED STORIES YOU MAY LIKE:

• How to add commas to numbers in Power BI [QUICK GUIDE]
• How to refresh data in Power BI [STEP-BY-STEP GUIDE]