Skip to main content
Microsoft 365
Subscribe
3 min read

6 updates in Power Query for Excel

By

This month’s Power Query update includes six new or improved features including:

  • Improvements to ODBC Connector
  • Navigator dialog improvements
  • Option to enable Fast Data Load versus Background Data Load
  • Support for Salesforce Custom environments in Recent Sources list
  • Easier parsing of Date or Time values out of a Text column with Date/Time information
  • Unpivot Other Columns entry in column context menu

Download Power Query

You can continue reading below for more details about each feature.

Improvements to ODBC Connector

We improved the ODBC Connector by making the SQL statement optional instead of required. Now users can simply provide the Connection String for their ODBC source and navigate the list of tables available from the data source, pick up the ones they want to import, and apply any additional transformations needed in the Query Editor. This greatly simplifies the process for bringing in data from several data sources supported via ODBC.

6 updates in Power Query for Excel 1

Navigator dialog improvements

With this update, we made several improvements to the Navigator dialog:

  • Ability to resize the Navigator pane—We made the list of databases/tables, in the left side of the Navigator dialog, resizable to improve readability.
  • Added the “Preview downloaded on…” message—On the Navigator dialog previews, below the table name, the new “Preview downloaded on…” message has been added to indicate the freshness of the data. In many cases, Power Query leverages the local cache for previews of tables that users have already imported. This might be confusing to users seeing outdated data. The “Preview downloaded on…” message addresses this confusion. The message is displayed for previews older than 24 hours. Note: if you want to retrieve the latest data preview before importing the data, it’s also possible to refresh this data preview from the top-right corner of the dialog.

6 updates for Power Query for Excel 2

Option to enable Fast Data Load versus Background Data Load

We added an option for users to control whether the query should be loaded to the workbook in the background or use the new “Fast Data Load” option to accelerate the query download. The default behavior is “Background Data Load,” but now users can instead choose the “Fast Data Load” mode in the Options dialog. When loading a query using the “Fast Data Load” mode, your query will take less time to load, however, Excel may be unresponsive for long periods of time during the upload.

cropped-6 updates in Power Query for Excel 3

Support for Salesforce Custom environments in Recent Sources list

Last month we added a new option to the Salesforce connector to specify whether to connect to Production or a Custom environment. This month we are enabling the Salesforce Custom environments to be shown in the Recent Sources list.

Easier parsing of Date or Time values out of a Text column

We made it much easier to extract a Date or a Time value from a text column that contains a DateTime or DateTimeTimezone value represented as Text. Before this update, you would need to convert your Text column to DateTime or DateTimeTimeZone and then extract the Date or Time component.

In this month’s update, we’ve added two new options under the Transform and Add Column tabs, in the Date & Time dropdown menus, to parse these data types out of the currently selected Text columns.

6 updates in Power Query for Excel 4

Unpivot Other Columns entry in column context menu

“Unpivot Columns” is one of the most popular transformations in Power Query. However, what many users don’t know is that “Unpivot Other Columns” is even better! “Unpivot Other Columns” helps users ensure their queries automatically pick up new columns added to the original data in the future. This is the most common case for tables where new columns are added for each new period of data (i.e. years, quarters). Users only need to select the columns that are “stable” and select Unpivot Other Columns—from that point on all other columns in the table will be unpivoted.

“Unpivot Other Columns” has been available for a while in the Transform tab, under Unpivot Columns, but now we also added it to the column context menu in the preview for easier access and discoverability.

6 updates in Power Query for Excel 5

That’s all for this month. We’re making lots of incremental improvements to Power Query and we hope that you find it better with every new monthly update. Please continue sending us feedback using our “Send a Smile/Frown” feature, or by voting for what you’d like to see next.

Download Power Query

—Miguel Llopis, program manager on the Power Query team.

Power Query for Excel is available with an Office 365 ProPlus subscription, Office 2010 Professional Plus with Software Assurance, Office 2013 Professional Plus or Excel 2013 Standalone. Power Query for Excel is also available in all other Excel 2013 Desktop SKUs with reduced functionality. Check out the System Requirements section on this page for more details.

Download the add-in and learn more about getting started.