This month’s Power Query update includes six new or improved features including:
You can continue reading below for more details about each feature.
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.
With this update, we made several improvements to the Navigator dialog:
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.
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.
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.
“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.
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.
—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.
Help people and teams do their best work with the apps and experiences they rely on every day to connect, collaborate, and get work done from anywhere.
Leave a Reply