Resolve data import errors
Occasionally, you may see errors after you load data in Power BI. If you followed previous examples, you would see the message shown in Figure 1-37 after you loaded data.
FIGURE 1-37 Apply query changes information
Note Companion Files
If you want to follow along with the example, open 1.2.9 Errors.pbix from the companion files folder.
Errors in this context refer to error values. Error values by themselves don’t prevent queries from loading—you’re merely notified about the number of error values for each query. Error values are loaded as blank values.
To view the rows that contain errors in Power Query, you can select the View errors hyperlink. This opens Power Query Editor, and it creates a new query called Errors in Customer, which contains one row. If you check the last column, Postal Code, you’ll see the error you encountered earlier, shown in Figure 1-38.
FIGURE 1-38 Postal Code error
The error happens because Power Query is trying to convert the “N/A” value, which is text, into a number. You can fix the issue by changing the data type of the Postal Code column as follows:
Select the Customer query.
Right-click the Postal Code column.
Select Change Type > Text.
Select Replace current.
If you return to the Errors in Customer query, you’ll see that it’s empty. To clean up the queries, you can delete the group that starts with Query Errors.
After you select Close & Apply, you’ll see no error messages.
Chapter summary
- Power BI has over 100 native connectors, with more being added all the time, and you can create your own connectors, too.
- You can change the data source settings, which include the location of the data source and the permissions associated with it. Changes apply to all queries that use the data source.
- Power BI Desktop performs best and allows you to use most of its features when you import data. In some cases, importing data is not feasible—for example, when there is too much data to import, or when data is updated very frequently and business requirements demand always showing the latest data. These issues can be addressed if the data source supports the DirectQuery connectivity mode. With DirectQuery, no data is imported into Power BI. Instead, all data remains in the source, and every time Power BI needs to calculate values, it sends queries in the data source’s native query language. In some cases, you can apply certain types of transformations that can be translated to the native query language. There is a special case of DirectQuery called Live Connection, which is available with Analysis Services and Power BI service data models.
- You can create composite models by connecting to more than one data source in DirectQuery or by combining imported data and DirectQuery.
- You can connect to Microsoft Dataverse and dataflows, which use Power Query in the cloud. You can also create dataflows in the Power BI service.
- You can use parameters in your queries, which help you avoid making changes in several places manually. Parameters can also be useful to create your own functions.
- Power Query offers several ways to profile your data, which includes column statistics and distribution, as well as the number of errors.
- Some of the most common operations you can perform in Power Query are
- Replacing values
- Reducing rows
- Adding, removing, and splitting columns
- Changing column type
- Combining queries
- Pivoting and unpivoting columns
- Queries that don’t need to be loaded can stay as queries if you disable their loading.
- If you see errors after loading your data, Power Query can show you the rows that contain errors, allowing you to correct them.