Part V: Understanding Power Pivot and Power Query
If you’d rather handle data type changes without the help from Power Query’s type detec-
tion feature, you can turn it off. Click Data ➪ Get Data ➪ Query Options to open the dialog
box shown in Figure 41.9. Select Data Load under Current Workbook and then uncheck the
option to Automatically detect column types and headers for unstructured sources.
FIGURE 41.9
Disabling the Type Detection feature
Avoiding Power Query Performance Issues
Because Power Query inherently paves the way for large amounts of data with fairly liberal
restrictions, it’s possible to end up with queries that are unbearably slow.
When you’re wrangling a few thousand records, query performance is not an issue.
However, when you are importing and crunching hundreds of thousands of records, perfor-
mance becomes an issue. There is no getting around the fact that the larger the volume of
data, the slower your queries will run. That being said, there are steps that you can take to
optimize query performance.
Using views instead of tables
When connecting to an external database, Power Query allows you to import views as well
as tables. A view is essentially a predefined query on the server itself.
While tables are more transparent, allowing you to see all of the raw unfiltered data, they
come with all available columns and rows—whether you need them or not. This often forces
you to take extra steps and processing power to remove columns and filter out data that
you don’t need.
