Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Resolve Data Import Errors

  1. Home
  3. Resolve Data Import Errors

We shall discuss some of the more common import error messages that you might encounter in Power BI. When you are importing data into Power BI, there can be cases when errors may occur, from factors including –

  • Firstly, Power BI imports from numerous data sources.
  • Secondly, every data source might have dozens (and sometimes hundreds) of different error messages.
  • Then, there can be other components can cause errors, such as hard drives, networks, software services, and operating systems.
  • Lastly, data can often not comply with any specific schema.

Steps to Resolve Data Import Errors

In Microsoft, data import errors can occur when importing data from external sources into applications such as Excel, Access, or Power BI. Here are some steps you can take to resolve data import errors in Microsoft:

  1. Check data format: Ensure that the data you are trying to import is in the correct format and matches the expected format of the application. For example, Excel requires that data is organized in rows and columns with a header row, and that dates and numbers are in the correct format.
  2. Check data source: Verify that the data source is valid and accessible. If the data source is a file, make sure the file exists and that you have the necessary permissions to access it. If the data source is a database, check that the database is running and that you have the correct credentials.
  3. Check data range: Make sure that you are importing the correct range of data. In Excel, you can use the Import Data dialog box to specify the range of cells to import.
  4. Check delimiter: If you are importing data from a file, check that the correct delimiter is selected. Delimiters are characters used to separate values in a file, and the wrong delimiter can cause the data to be imported incorrectly.
  5. Check data mapping: Verify that the data mapping is correct. Data mapping is the process of matching the fields in the source data to the fields in the destination application. Make sure that the fields are mapped correctly and that there are no mismatches.
  6. Check data type: Ensure that the data type of each field is correct. For example, if a field contains numbers, make sure the data type is set to numeric and not text.
  7. Use import wizard: If you are still having trouble importing data, try using the import wizard provided by the application. The import wizard will guide you through the process step-by-step and may help you identify the source of the error.

By following these steps, you should be able to resolve most data import errors in Microsoft applications.

Query Timeout Expired 

Often the relational source systems indulge many people together, concurrently using the same data in the same database. Moreover, there are some relational systems and their administrators who wish to restrict a user from monopolizing all hardware resources by setting a query timeout. Also, these timeouts can be configured for any time span, from as minimum as 5 seconds to as much as 30 minutes or more.

Example – When pulling data from your organization’s SQL Server, you might encounter the error below.

data import errors for query timeout
Source: Microsoft

The “Query Timeout Expired” error message in Microsoft Query typically occurs when a query takes too long to execute and the query timeout period has elapsed. This can happen for a variety of reasons, including:

  1. The query is too complex and is taking too long to run.
  2. The database server is overloaded and cannot process the query in a timely manner.
  3. The network connection between your computer and the database server is slow or experiencing connectivity issues.

To resolve this issue, you can try the following:

  1. Increase the query timeout period. In Microsoft Query, go to the “Options” menu and select “Timeouts”. Increase the value of the “Query Timeout” setting to a higher value, such as 120 seconds.
  2. Optimize the query. Try to simplify the query by removing unnecessary joins or conditions, or by breaking it down into smaller queries. You can also try running the query directly in the database management system to see if it performs better.
  3. Check the database server performance. If the server is overloaded, consider adding more resources, such as RAM or CPU, or optimizing the database configuration.
  4. Check the network connectivity. Make sure that the network connection between your computer and the database server is stable and fast. You can try pinging the server to check for any latency or packet loss issues.

If none of these solutions work, you may need to seek help from a database administrator or IT specialist who can troubleshoot the issue further.

Power BI Query Error: Timeout expired

Power BI Query Error showcases that you have pulled too much data according to your organization’s policies. Moreover, Administrators incorporate this policy for avoiding slowing down a different application or suite of applications that might also be using that database.

These errors can be resolved by pulling fewer columns or rows from a single table. Therefore, when you are writing SQL statements, it might be a common practice to include groupings and aggregations. Then, you can also join multiple tables in a single SQL statement. In addition, you can perform complicated subqueries and nested queries in a single statement. Also, these complexities add to the query processing requirements of the relational system and further, it considerably extends the time of implementation.

It is therefore suggested to consider taking small chunks of data and then bringing them back together by using Power Query, in case you require the rows, columns, and complexity.

The “Timeout expired” error in Power BI typically occurs when the query takes too long to execute and the server times out before the results are returned. There are several possible reasons for this error, including:

  1. Large data volume: If the query is trying to retrieve a large amount of data, it may take too long to execute, and the server may time out before the query completes.
  2. Complex query logic: If the query involves complex joins, aggregations, or calculations, it may take longer to execute, leading to a timeout error.
  3. Network connectivity: If there are network connectivity issues between the client and the server, the query may not complete within the allotted time.
  4. Server performance: If the server is experiencing high load or other performance issues, it may not be able to process the query within the timeout period.

To resolve this issue, you can try the following steps:

  1. Optimize your query: Try to simplify your query logic by removing unnecessary joins, filters, or calculations. Also, consider limiting the amount of data you’re retrieving by using filters or aggregates.
  2. Increase the timeout period: In Power BI Desktop, you can increase the query timeout period by going to File > Options and settings > Options > Current File > Query Options and increasing the value of the “Command Timeout” setting.
  3. Optimize your network connectivity: Ensure that your network connectivity is stable and that there are no network-related issues causing the query to time out.
  4. Optimize your server performance: If the server is experiencing performance issues, consider optimizing the server configuration, upgrading hardware, or using a more powerful server.

By following these steps, you can troubleshoot and resolve the “Timeout expired” error in Power BI.

No data formatted as a table 

In general, you may encounter the “We couldn’t find any data formatted as a table” error while importing data from Microsoft Excel. Now, this error is self-explanatory and Power BI expects to find data formatted as a table from Excel. Further, the error event tells you the resolution. Some of the important steps to resolve the issue include,

  • Firstly, open your Excel workbook, and highlight the data that you want to import.
  • In the second step, press the Ctrl-T keyboard shortcut. The first row will likely be your column headers.
  • Then, verify that the column headers reflect how you want to name your columns. Then, try to import data from Excel again. This time, it should work.

The error message “No data formatted as a table” typically appears in Microsoft Excel when a user tries to perform an operation that requires data to be in a table format, but the data is not actually formatted as a table. This can happen if the user selects a range of cells and tries to use a table-related feature, such as sorting, filtering, or using a PivotTable.

To resolve this error, you can format your data as a table in Excel. Here’s how:

  1. Select the range of cells you want to format as a table.
  2. Click the “Format as Table” button in the “Styles” group on the “Home” tab.
  3. Choose a table style from the gallery or create a custom table style.
  4. Make sure the “My table has headers” option is selected if your data includes column headers.
  5. Click “OK” to apply the table formatting.

Once your data is formatted as a table, you should be able to use table-related features without encountering the “No data formatted as a table” error.

Could not find file Error

There are cases when importing data from a file, you may get the “Could not find file” error.   

The Could not find file error screen
Source: Microsoft

This error is generally caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings.

  • Firstly, open the Power Query by selecting the Transform Data button in Power BI.
  • Secondly, highlight the query that is creating the error.
  • On the left, under Query Settings, select the gear icon next to Source.
  • Lastly, change the file location to the new location.  

Data Type Errors 

There are times when you import data into Power BI, the columns appear blank. Now, this situation occurs when interpreting the data type in Power BI. But, the resolution to this error is unique to the data source. For instance, if you are importing data from SQL Server and see blank columns, you could try to convert to the correct data type in the query.

Instead of using this query: SELECT CustomerPostalCode FROM Sales.Customers

Use this query: SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers

Therefore, by specifying the correct type at the data source, you can eliminate many of these common data source errors.

You may encounter different types of errors in Power BI that are caused by the diverse data source systems where your data resides. If you encounter an error that was not discussed in the preceding sections, you can search Microsoft documentation for the error message to find the resolution you need.

Microsoft Power BI Data Analyst (PL-300) free practice test
Menu