What’s new in Power Query

Power Query is a data analysis feature available for Excel that helps you discover, combine and refine data. With Power Query, you can combine and shape data from many different sources, and unlock insights that were previously buried or hidden. Power Query includes intuitive discovery features, a powerful query language, and a built-in ability to share your discoveries with others.

What's New in Power Query

Important   You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it.

Each month Power Query is updated with new capabilities and functionality. The following section describes the most recent updates, and below the current updates are updates included in previous releases of Power Query.

The Current Update

Select the following button to download the current Power Query release:

Download Power Query

Release Date: May, 2015

Release Version: 2.23.4036.161

The following features are added to Power Query with this month's release. Below the following lists are details about each new or updated feature:

  • Enhanced Privacy Levels dialog

  • New Text Column filters: Does Not Begin With and Does Not End With

  • Improved Salesforce connectors

  • Improved Excel Workbook connector:

    • Automatic Column Type detection

    • Faster load for Data Previews

UPDATE DETAILS

Enhanced Privacy Levels Dialog - with this update, we enhanced the dialog that asks users to provide privacly levels for data sources involved in a query. You can now control whether privacy levels apply to a specific source (like a specific web page), or a more general source (like the entire web server). The following image shows whether privacy levels should be applied to a particular page, or to an entire site.

Privacy Levels

New Text Column Filters- now you can filter text columns by two useful criteria: Does Not Begin With and Does Not End With. Prior to this update, custom formula editing was required to get this functionality, but now it's easy. Simply select either from the Text Filters menu. The following screen shows where you can find the new filters.

Power Query column filters

Improved Salesforce connectors - many of our customers who use the Salesforce connecter told us they wanted to connect to a custom domain first, rather than connecting to the default (production) domain. They also told us they wanted the chance to create relationships when loading Salesforce tables to the Data Model, based on the relationships that already exist in Salesforce. Prior to this update, those customers would have to modify underlying formulas, so it could be done, but we wanted to make it easy for them.

With this update, the two Salesforce options (connect to a custom domain, and create relationships) are available from a dialog box. The option to import relationships between Salesforce tables is available in the Salesforce Objects connector, as shown in the following figure.

Salesforce updates

Selection of a custom domain, rather than the production domain, is available in both the Salesforce Objects dialog box, (shown above) and in the Salesforce Reports dialog, shown below.

Salesforce updates

Improved Excel Workbook connector - this is one of our most popular connectors. It lets you import tables, named ranges, or raw worksheet data from an Excel workbook. In this update we added automatic column type detection for the Excel Workbook connector, so users don't have to manually convert imported columns to the right data type. We also improved performance for the Excel Workbook connector, so data previews in the Navigator and Query Editor load much faster.

That's it for this month. Check back soon for more updates - we're always working on the next great things for Power Query.

Previous Updates Section

Note   Updates to Power Query are cumulative; each current release include updates from previous months.

Updates for April, 2015

Release Date: April, 2015

Release Version: 2.22.4007.242

The following features are added to Power Query with this month's release. Below the following lists are details about each new or updated feature:

  • Power Query availability for all Excel 2013 Desktop SKUs

  • OData V4 support

  • Unified Options dialog

  • Option to disable Native Database query prompts

  • Support for Custom ADFS Authentication Services

  • Updated Facebook connector due to Facebook API changes

  • Support for Fixed Decimal Number type

  • Alternate Windows Credentials

  • New ribbon location and name for Online Search (now called Data Catalog Search)

  • New Transformations:

    • Remove Blank Rows

    • Median Operation available for Group By and Aggregate Column

    • Convert DateTimeZone value to Local Time

  • Performance improvements for loading medium and large datasets

Update Details

Power Query availability for all Excel 2013 Desktop SKUs - With this update, Power Query is available to all Excel 2013 Desktop SKUs. There are some differences in features, depending on which SKU of Excel 2013 is being used:

  • Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone: Full Power Query feature set

  • All other desktop SKUs get the full Power Query feature set, except the following data connectors:

    • Corporate Power BI Data Catalog

    • Azure-based data sources

    • Active Directory

    • HDFS

    • SharePoint Lists

    • Oracle

    • DB2

    • MySQL

    • PostgreSQL

    • Sybase

    • Teradata

    • Exchange

    • Dynamics CRM

    • SAP BusinessObjects

    • Salesforce

When Excel 2013 is launched, Power Query detects the SKU in use, and enables the appropriate set of features.

OData V4 support - this update adds support for OData V4 feeds. Use the existing OData Feed connector, which will now also accept feeds built using the latest OData version.

Unified Options dialog - we've unified Workbook Settings and Options into a single dialog, which is now organized by scope (current file versus global) and categories for easier navigation.

New Power Query dialog

Option to disable Native Database query prompts - it can be useful to provide a custom SQL statement when connecting to an existing database, especially for customers who already have complex SQL queries for pulling dataand want to leverage that when getting started with Power Query.

However, there's potential risk that such queries contain malicious SQL code, and could delete or modify content in a database when executed. Because of this risk, Power Query displays a security prompt whenever users try to run a native database query outside of the data source dialogs. Many customers use this approach with Custom Columns or similar scenarios to dynamically build and execute native database queries. The drawback is that security prompts for every distinct SQL statement appears, requiring approval for each prompt, which is very inconvenient.

To improve the experience for customers taking this approach, this month's update includes an option to disable Native Database Query security prompts.

Security Note   Only disable the Native Database Query prompts if necessary, use this feature with care, and beware of the potential risks described in the previous paragraphs.

The option to disable Native Database Query prompts is found in the Options dialog, in Global > Security.

Native Database Queries option

Support for Custom ADFS Authentication Services - with this update you can use custom ADFS authentication endpoints through our Organizational Account credential type, enabling access to data sources that require ADFS authentication, such as some on-premise instances of Dynamics CRM. Once Power Query is registered by your administrator, you can approve a custom endpoint when prompted for access. You can also manage the list of already-approved endpoints within the Options dialog, in Global > Security, as shown in the following image.

New ADFS authentication

Updated Facebook connector due to Facebook API changes - as of April 30th 2015, Facebook will expire v1.0 of its Graph API. The Graph API is what Power Query uses behind the scenes for the Facebook connector, enabling you to connect to your Facebook data and analyze it. Unfortunately, the expiration of its v1.0 Graph API means some changes in the Power Query Facebook connector as it currently exists. The most important difference is the set of permissions we’re able to leverage and the data those permissions return. For example, Friends Lists and News Feeds are commonly used permissions that are now changed or inaccessible.

Queries that were built before April 30th may no longer work, or may return less data. After April 30th, Power Query will leverage the Facebook Graph API v2.2 in all calls to the Facebook API. You’ll likely need to authenticate again, to approve the new set of permissions that Power Query asks for. You can also get more details on the change in the Facebook API.

Support for Fixed Decimal Number type - this update introduces support for Fixed Decimal Number type. This new type can be found in the Data Type menu within the Home tab and the Transform tab in the Query Editor, as well as in the Change Type column context menu.

Alternate Windows Credentials - you can now specify Alternate Windows Credentials (rather than the current user) under the Windows credentials option in the Credentials dialog, as shown in the following image.

New Alternate Windows Credentials option

New ribbon location and name for Online Search (now called Data Catalog Search) - we've renamed Online Search to Data Catalog Search, and moved it from the Get External Data group to the Power BI group in the Power Query ribbon tab. The following image shows the new name and location in ribbon.

New location and name for Data Catalog Search

New Transformations - we continue making improvements to the set of transformations supported in Query Editor. This month the following new transformations are added (images of each new transformation are provided below each entry):

  • Remove Blank Rows

    Remove Blank Rows
  • Median operation available for Group By and Aggregate Column

    New Median Operation
  • Convert DataTimeZone value to Local Time

    Convert DataTimeZone to Local Time

That's it for this month. Check back soon for more updates - we're always working on the next great things for Power Query.

Updates for March, 2015

Release Date: March, 2015

Release Version: 2.21.3975.261

The following features are added to Power Query with this month's release:

  • Google Analytics connector

  • Additional operators for data filtering in Query view

  • Automatic Model Relationship Detection

  • Enhanced Add Relationship dialog

  • 40-50% performance improvement filling database tables without filters/transforms

  • Lots of bug fixes

Updates for February, 2015

Release Date: February, 2015

Release Version: 2.20.3945.102

The following features are added to Power Query with this month's release:

Performance Improvements

Dynamics CRM Online connector

Note   Currently, only URLs within the crm.microsoft.com domain are accepted by the Dynamics CRM Online connector dialog. This does not include non-production tenants. A fix for this issue is anticipated in the March update. The temporary workaround is to connect to this feed using From OData.

Navigator Dialog Improvements

  • Better preview experience for multi-dimensional sources (Analysis Services and SAP BusinessObjects)

  • Show Selected Items option

  • Improved Search capabilities in the Navigator tree

New Transformations

  • Age and Subtract operations for Date/Time columns

  • Aggregate Columns: Option to disable new columns’ prefix

Field List improvements

  • Expand/Collapse tables

  • Hide/Unhide fields

  • Optimized layout (spacing, margins, and fonts)

Report Pages Navigation - Keyboarding support

Lots of bug fixes

Updates for January, 2015

The January 2015 updates to Power BI are in Power Query and Power Map.

Power Query

The Power Query updates for January 2015 are the following:

  • ODBC Connector

  • From Azure ribbon drop-down menu

  • Workbook Settings dialog

  • Option to enable or disable Relationship Detection

  • Column and Row counts in Query Editor Preview

  • Confirmation dialog to delete a query when deleting a worksheet

The following sections describe the updates in detail. You can always watch a video of the January 2015 updates, too.

ODBC Connector – You can now connect to generic ODBC providers using Power Query, enabling users to import data into Power Query from several data sources that are not natively supported. Connect and import data from ODBC-based data sources by specifying the connection string parameters and a SQL statement to execute.

ODBC driver

Note    Credentials should be entered on the credentials page, rather than in the Connection String field of the ODBC Data Source window, to ensure credentials are not inadvertently shared with the query.

Power Query cannot optimize query steps after the SQL statement, so you should optimize the original statement as much as possible (include “group by” or “join” operations, for example).

From Azure ribbon drop-down menu – Power Query continues to add new data sources, and as a result, it was time to rearrange the drop-down menus to make them easier to navigate. With this month's release, there is a new From Azure drop-down menu, where you can find Power Query's Azure-related data sources (previously, they were accessed from the From Other Sources drop-down menu).

From Azure drop-down

Workbook Settings dialog – In this update, Workbook Locale and Fast Combine settings have been moved into a new Workbook Settings dialog window (they used to be accessed from the ribbon). In addition, a new Relationships Detection option is also included in the Workbook Settings window (more about that in the following section). Moving these settings into their own dialog window lets us provide better descriptions for each of them.

Workbook Settings dialog

Enable or disable Relationship Detection – Power Query attempts to detect relationships when loading queries into the Data Model. Such relationships may exist in the data source itself (such as in SQL Server), or might be explicitly created by a user as part of the query steps; for example, when merging two queries, Power Query automatically detects a relationship between the queries, and that relationship is created when the queries are loaded into the Data Model.

Detecting relationships is a useful feature, but may cause increased load time for queries. Often users don't need those relationships, or know that no relationships exist, and can improve query load time by disabling relationship detection. To give you the most flexibility possible, we've added the option to disable relationship detection for a workbook.

Note    Relationship detection is on by default, and is workbook-specific. You can modify the default behavior for each workbook in the Workbook Settings dialog.

Column and Row counts in Query Editor Preview – Query Editor Preview now provides the number of columns and rows being displayed in the Query Editor Preview window. Providing column and row counts can help you get a sense for the shape and size of the data, while defining transformations in your queries.

Column and Row counts

Note   The column and row counts are based on the Query Editor Preview, and not based on the full data set. In most cases, the actual row count of the imported data will be much larger than what is displayed in Query Editor Preview. The number of displayed rows will change as more or fewer rows are displayed in the preview (for instance, the row count will increase when you scroll down in the preview).

Confirmation dialog to delete a query when deleting a worksheet – Prior to this update, when you deleted an Excel worksheet, all queries loaded into the worksheet automatically deleted as well. This sometimes caused accidental data loss, when users were unaware of that default behavior.

Based on feedback, we've added an additional confirmation to clarify how queries are handled when an Excel worksheet is deleted. There are two choices: delete the query, or disable loading the query into worksheet.

Delete query window

Updates for November, 2014

The November 2014 updates to Power BI are all in Power Query.

Power Query

The Power Query updates for November 2014 are the following:

  • SQL Server Analysis Services connector

  • Salesforce Reports & Objects connector

  • Expand Columns – Ability to disable/customize column prefix

  • Improved File Menu options in the Query Editor

  • Access Advanced Editor in the Queries pane inside the Query Editor

The following sections describe the updates in detail. You can always watch a video of the November 2014 updates, too.

SQL Server Analysis Services connector – You can now connect to Analysis Services cubes through Power Query. This is a highly anticipated addition to Power Query, and it provides exploration, transformation and import capabilities on top of Analysis Services cubes.

When using the connector, Power Query translates user actions into MDX queries against the source. The connector also works for tabular instances, but is optimized for multi-dimensional experience.

The new connector is located in the “From Database” menu. Once connected, you can browse cubes and pick the desired dimensions, measures, hierarchies or KPIs, then filter and transform the data before loading it to the worksheet or Data Model.

AS Cubes

Salesforce Reports & Objects connector – The Salesforce connector is now a standard Power Query feature (it's been in Preview since early October). This feature lets you connect to your Salesforce accounts and import data into Excel. There are two entry points offered: Salesforce Objects and Salesforce Reports.

Salesforce

Expand Columns – Ability to disable or customize column prefix – In response to feedback, users now have control over the column name prefix that's used during an Expand Column operation. Previously, new columns contained the original column name as prefix, and users would have to manually remove it from each column (either using Column Rename or by modifying the generated Expand Column formula). With this update, a new checkbox is available in the Expand Column menu that lets you disable the prefix.

Expand Columns

In addition, you can change the prefix using the Expand Columns dialog.

Expand Columns

Improved File Menu options in the Query Editor – The Query Editor File menu now lets you access Options and Settings without having to leave the Editor. Workbook Settings are not accessible from the File menu yet - we're hoping to enable that in next month’s update.

New menu item

Access Advanced Editor in the Queries pane inside the Query Editor – It's now much easier to access the Advanced Editor dialog when working with multiple queries in the Query Editor. Just right click the query you want to edit, and select Advanced Editor in the context menu. It's that easy.

Advanced Editor

Updates for October, 2014

The October 2014 updates to Power BI are all found in Power Query. The following section details what's new in Power Query for this month.

Power Query

The Power Query updates for October 2014 are the following:

  • New Transformation: Use Headers as First Row

  • Column Name field in Merge Columns dialog

  • Stale Data warning in new queries

  • Quick Access Toolbar now available in Query Editor

  • Function Invocation Experience from Search

  • Preserve Excel Number formats on refresh

The following sections describe the updates in detail. You can always watch a video of the October 2014 updates, too.

New Transformation: Use Headers As First Row – It's now easier to demote the first row of headers, which has been a frequent request. The Query Editor now includes a ribbon button to Use Headers as First Row, directly below the Use First Row as Headers button. The following image shows the new ribbon button.

PQ updates

Column Name field in Merge Columns dialog – The Merge Columns dialog now lets you immediately name the merged column. In future updates, the option to name new columns will be added as well, such as when expanding a column or aggregating columns. The following image shows the new dialog.

PQ updates

Stale Data warning in new queries – A warning now appears whenever a loaded query is more than 24 hours old, and includes the approximate age of the query (how long Power Query has stored it). This information was previously displayed in the Status Bar of Query Editor, but now is prominently displayed as a warning.

Power Query uses the local preview cache, which improves performance and responsiveness, but many users reported believing that the preview data was wrong, when it simply had not been recently refreshed.

To address the warning, users can choose to refresh the query to update the preview, or can dismiss the warning by beginning to work with the existing preview data. The following image shows the new warning.

PQ Update

Quick Access Toolbar now available in Query Editor – Query Editor now has a Quick Access Toolbar, letting you put your most common tasks just a click away. By default, the toolbar contains the Send Smile/Frown menu, so you can easily provide us with feedback (which we always like and appreciate).

The following image shows the new Quick Access Toolbar.

PQ update

To make it really easy to add your most common tasks to the toolbar, adding items to the Quick Access Toolbar is as easy as right-clicking the command's ribbon button item, then selecting Add to Quick Access Toolbar, as shown in the following image.

PQ Updates

Function Invocation Experience from Search – You now can invoke query functions directly from online search, rather than having to load them into a workbook and invoke from there. This is designed to simplify your search and query function experience, and streamline your efforts.

You can also add the function query to your workbook, which lets you invoke the query function as many times as you like. The following image shows a search that returns a query function.

PQ update

Preserve Excel Number formats on refresh – Customization to the Number format of Excel worksheet cells that are part of a Power Query table are now retained after performing a refresh. This is an often-requested change of behavior from previous releases.

The following image shows how the Number format remains the same after a refresh

PQ update

Updates for September, 2014

The September 2014 updates to Power BI are all found in Power Query. The following section details what's new in Power Query for this month.

Power Query

The Power Query updates for September 2014 are the following:

  • Improved Data Source Settings dialog

  • Improved Insert Index Column transformation

  • Additional options in Query Navigator in the Editor (reorder, delete, group)

  • Option to disable Power Query Update notifications

The following sections describe the updates in detail. You can always watch a video of the September 2014 updates, too.

Improved Data Source Settings dialog – Significant improvements to the Data Source Settings dialog were made with this update, addressing many reported gaps in its functionality. With this update, the Data Source Settings dialog lets you do the following:

  • Sort data sources by name/path or data source type

  • Search within the list of data sources

  • Select multiple items and delete them, or edit the Privacy Level for all selected items

The following image shows the new Data Source Settings dialog:

Improved Data Settings

In addition to improving the Data Source Settings dialog, its Edit dialog now lets users perform the following tasks:

  • Edit or delete stored credentials

  • Control whether Power Query should encrypt connections to the selected data source

  • Modify (or even set, for the first time) the Privacy Level for the selected data source

  • For database sources, determine whether any Native Query has been approved, and revoke all approvals

The following image shows the Edit dialog:

The Edit dialog for Data Source Settings

Improved Insert Index Columntransformation – New Index Columns in queries can now be customized, rather than starting at zero, and the increment for each subsequent row can also be specified. The following image shows the new feature:

Customize a new Column Index

Additional options in Query Navigator – The previous month's updated introduced the Query Navigator inside Query Editor. In this month's updates, the Query Navigator can now be used to reorder queries within the list, delete queries, or create and manage query groups.

New capabilities in the Navigator pane

Option to disable Power Query Update notifications – Updates are great, but so is flexibility. This update includes an option to disable update notifications in the Options dialog. The following nstaller command line argument also disables the system tray update notifications:

PowerQuery.msi DISABLE_UPDATE_NOTIFICATION=1

The following image shows the new checkbox, in the Options dialog, that disables notifications.

Disable update notifications

Updates for August, 2014

The August 2014 updates to Power BI are primarily found in Power Query. Other features will provide updates as their features are released to the public. There are many Power Query updates this month, as shown in the following section.

Power Query

This month's updates include new features and usability improvements to existing features. As always, you can find additional information about Power BI at www.powerbi.com. The following updates are included in the August 2014 release:

  • Support for loading Binary-typed columns (such as images) to the Data Model

  • Multi-query editing in the Query Editor

  • Pivot Columns

  • Automatic type coercion for Text operations over any scalar column type (number, date, etc.)

  • Query Properties dialog for editing name and description

  • A simplified Share Query experience

  • Enable Tracing option in the Options dialog

The following sections describe each of these new features or updates in detail. And if you're interested, you can watch a video that shows this month's collection of updates, too.

Support for loading Binary-typed columns (such as images) into the Data Model – this update enables users to load Binary-typed columns into the Data Model from Power Query. Users can download blobs of data into the Data Model from data sources that support the data type (such as SQL Server), and leverage existing Power Query library functions (such as File.Contents and Web.Contents) to load and store contents from a specified location into the Data Model. The following image shows the Query Editor window with a Picture (image) field as part of the query. Power Query

Once a table containing a Binary column is loaded from Power Query into the Data Model, the following two steps need to be performed from the PowerPivot window:

  1. Open the Table Properties dialog from the Design tab in PowerPivot and enable the column containing Binary data.

  2. Customize the Table Behavior in the Advanced tab, to indicate that your Binary data column is the Default Image.

After performing these two steps, the images can be visualized in a Power View report, similar to following image.

Power View with BLOBs

Multi-query editing in the Query Editor – The Query Editor window now has a Navigator pane, letting users quickly switch between queries, and perform changes to any query, without having to close Query Editor or consult the Workbook Queries task pane. The Navigator pane reflects the list of queries and query groups in the current workbook, similar to the Workbook Queries task pane. The following image shows the Navigator Pane in the Query Editor window.

Power Query Navitator pane

Pivot Columns – This month's release includes first-class User Interface support for Pivot Columns. Prior to this update, Pivot Columns operations were performed by typing the corresponding library function (Table.Pivot), which wasn't very discoverable, and was fairly difficult due to the numerous required parameters. With this update, users can simply use the Pivot Columns button in the Ribbon, as shown in the following image.

Power Query - Pivot Columns button in the Ribbon

With a column selected, clicking the Pivot Column button presents options to customize the values in that column, and the Aggregation function to apply to those values. There's also a Help link for more information and examples about pivoting columns in Power Query.

Power Query - Pivot Column dialog

Automatic type coercion for Text operations over any scalar column type (number, date, etc.) – With this release, column-level transformations become easier. Power Query can now perform Text transformations (such as split/combine columns, uppercase/lowercase, and others) on top of any scalar-type column such as numeric (Whole or Decimal Numbers) or date and time columns. When transforming an existing column, or inserting a new column based on existing data, Power Query automatically handles column type conversions so that the Text operations behave as expected. This reduces the need to explicitly change column types in preparation for additional column transformations. The following image shows the ribbon commands that enable the automatic coercion.

Power Query - automatic text coercion

Query Properties dialog for editing the name and description – In response to popular demand (customer feedback), this update includes a new Properties dialog to change the name or description of a query. The Properties dialog is accessible from the ribbon, in the Query tab, and from the Workbook Queries task pane.

The Power Query Query Properties dialog

The Properties dialog is also accessible from Query Editor, on the Home tab of the ribbon, as shown in the following image.

Power Query Properties within Query Editor

Simplified Share Query Experience – To clarify and streamline the process of sharing a query, the terminology associated with Share Query is now more descriptive about the product behavior. Users can send a point-in-time copy of the query to the Power BI data catalog, share it with an specific set of users, groups of users, or their entire organization. The input fields in the dialog are now divided between two tabs: Query, which are properties related to the query being sent to the catalog; and Sharing, which specify who should be able to find the query in the Power BI Data Catalog. The following two images show the Query dialog, and the Sharing dialog, respectively.

Share Query - Query dialog Power Query - Share query SHARING dialog

To manage Power BI Data Catalog queries, use the My Data Catalog Queries ribbon button, found in the Power BI group in the Power Query tab, as shown in the following image.

Manage your queries in the Data Catalog

The Enable Tracing option in the Options dialog – In this release, it's a lot less painful to send Power Query traces to Microsoft. In the Options dialog, a simple checkbox near the bottom turns on tracing, as shown in the following image.

The new Tracing checkbox option

Tracing is automatically disabled when all Excel sessions are closed, to avoid unnecessary use of local disk space.

Updates for July, 2014

There are many updates this month, in various feature and related areas of Power BI. The following sections also include links to additional information, including blog posts. Where a screen shot is helpful in describing the update, we’ve included one as well.

You can find additional information about Power BI at www.powerbi.com.

Power Query

The most recent collection of Power Query updates are included in the Power Query download, which you can get here. The following updates are included:

  • More Flexible Load Options

  • Query Groups

  • Improved Error Debugging Experience

  • Additional Query Editor Transformations

  • Defining Math Operations based on a Single Column

  • Options Dialog

  • Update Notifications

The following paragraphs provide an overview of each update; for more detailed information about each update, please visit the Power Query blog. There you’ll find screen shots, and a video that details these updates.

In addition, Power BI administrators can now allow a SharePoint Online Document Library to be searchable from within Power Query. More information on that update is included in its own section, at the end of this month’s updates.

More flexible Load Options – many customers are interested in increased control over how and where queries are loaded within a workbook. The new Load Options dialog lets you customize whether a query should be loaded to the worksheet or Data Model, and whether to load into an existing worksheet (rather than loading into a new worksheet). Selecting whether the query should be loaded as a table, or simply as a connection, is also clearer with this release – and those selections are available wherever it’s possible to load a query (such as Search Results, the Navigator pane, Query Editor, and others). The following image shows Query Editor with a binary Picture object as a loaded column.

Query Groups – with this update, you can select multiple workbook queries (CTRL+Click), and create custom groups. With custom groups, you can more easily organize and classify queries, or perform bulk operations to queries within a group.

Improved Error Debugging Experience – the indicator that identifies “Number of rows with Errors” is now a hyperlink that displays a preview of the rows with errors, allowing you to explore and interact with the results. It also includes a row index, to help clarify where the errors appear.

Additional Query Editor Transformations – you can now replace errors in a column, rather than ignoring the rows with errors, from the TRANSFORM tab with Query Editor. A dialog window appears, where you can provide the value with which to replace the errors.

Defining Math Operations based on a Single Column – customers often need to add new columns to apply math operations. With this release, math operations can be created by selecting just one column.

Options Dialog – you can now reset Power Query to its default behaviors from the Options dialog.

Update Notifications – while we want you to be aware of updates to Power Query, we’ve heard feedback about the how frequently the system tray Power Query update notifications appeared, and we listened. With this update, we’ve limited the system tray update notifications to three occasions per update, and no more than once per day.

Power View

This month’s updates to Power View include new functionality for Power View for HTML5. The updates include the following:

  • Updates to bubble and scatter charts with a Play axis

  • Filtering Updates

  • Updates to Small Multiples

Updates to bubble and scatter charts with a Play axis – in bubble and scatter charts with a Play axis, the following updates allow you to:

  • Tap different locations on the play axis to switch to different frames of the chart

  • Highlighting values in other visualizations cross-filters bubble and scatter charts with a play axis

  • Slicers and filters in the Filters area also filter bubble and scatter charts with a play axis

  • Drill down, and back up, on bubbles in bubble and scatter charts with a play axis

Please note that highlighting values in bubble and scatter charts does not cross-filter other visualizations on the sheet.

Filtering Updates – in the Filters pane, you can now edit advanced test and numeric filters.

Updates to Small Multiples – you can now drill down, and back up, and highlight values in small multiples to cross-filter other visualizations on the sheet.

The following screen shows a collection of small multiples, with a drill-down selected.

Data Management Gateway

This month’s updates to the Data Management Gateway (version 1.2) include many different data sources for schedule data refresh, and the ability to group multiple gateways into one gateway group:

  • Schedule data refresh for workbooks with over twenty different data source types

  • Group multiple gateway instances for better load balancing and availability

Scheduled Data Refresh – users can now schedule data refresh for workbooks with Power Query connections to over twenty different data source types. To see the complete list of data sources, please see this article.

The following screen shows a data refresh for an OData feed:

OData feed and data refresh

Group Multiple Gateways – to improve load balancing and availability, administrators can now add multiple gateway instances into one group. By representing multiple gateways to one group, users can specify the group as the gateway, and thereby have automatic failover within that group; if one gateway fails, the others in the group are automatically selected to ensure the user maintains connectivity.

The following screen shows three gateway instances included in one group:

Group multiple gateways

Search SharePoint Online Document Library from Power Query

Power BI Administrators can now allow a SharePoint Online (SPO) Document Library to be a searchable data source from within Power Query. Excel workbooks within that document library become available data sources that are displayed within the query results.

The following screen shows the Power BI admin center, and the option to add a SharePoint Online Document Library as an exposed data source.

Power BI Admin Center - add a data source

The administrator enters the URL of the document library, and once authenticated, enables the SPO Document Library as a data source.

SPO search from Power Query

Updates for April, 2014

The following lists describe the updates to Power BI for Office 365.

Admin Center

  • Improved the description of how to get a connection string from an Excel workbook.

Data Management

Improvements to how you manage data sources:

  • In the Data Sources list, each data source displays a color-coded status of Complete, In Complete, or Missing. This makes it easier for you to manage data sources.

  • You can filter the Data Sources list by my queries data sources or all data sources.

Q&A

Did you find a great insight using Q&A?  Now you can share your discovery using the Share by Email and Share a Link buttons to the right of the question box.

Updates for March, 2014

The following lists describe the updates to Power BI for Office 365, and for the Power Query and Power View business intelligence features of Excel.

Power Query

  • There’s a new Selection Well for multi-table import

  • Improvements were made to the layout of the Query Editor Ribbon

  • You now get a warning when trying to insert an intermediate step in your query

  • Improvements were made to Insert Custom Column

  • There’s a more informative message regarding Privacy Levels

  • Improvements were made to From Table selections

  • During Query Load, there are now distinctions between cancellation and errors

To download the most recent version of Power Query, visit the Microsoft Power Query for Excel download page.

Power View in HTML5, and Power BI Windows app

  • You can now view tiles

  • You can view database images

  • You can drill down, and back up, in a matrix

  • Improvements with interacting with tables and matrixes on a touch device

Power BI for Windows app

  • Additional languages – you can now view the app in nine languages

  • You can now view maps with a single series of data

  • Take advantage of Windows 8.1 capabilities

Q&A

Improvements to the SharePoint online experience:

  • Made it easier to fine-tune your Q&A result by consolidating controls for visualization type, filters, and additional fields on the right-hand side of the screen.

  • Featured Questions now appear on their own page.

Added navigation for common tasks right beside the question box for easy access.  For example, you can now navigate back to the featured questions collage.

Updates for February, 2014

Microsoft Power BI for Office 365 moved from preview to general availability.

Updates for January, 2014

Most of the updates for January focused on product quality improvements – over 250 product issues and improvements were made in this release, primarily focused on Power Query.

Power Query

The following functionality is new for Power Query:

  • More than 250 improvements and fixes were included in this release

  • Significant improvements to the Text/CSV import experience, including automatic detection of column delimiters

  • The Advanced Query Editing feature in Query Editor is now available in the View tab of the ribbon

Updates for December, 2013

Many updates were introduced in December. The following sections describe the updates.

Power Query

The following functionality is new for Power Query:

  • Power Query can now connect to more data sources:

    • Sybase IQ

    • Exchange

    • Dynamics CRM Online

  • When selecting multiple tables in the Navigator pane, Power Query automatically detects database relationships.

  • The Replace Shared query has been removed.

  • You can now Share your query from the Query fly-out

  • New Query Editor ribbon buttons were added for Fill Down, Sort Ascending/Descending and Number Transformations.

  • A new Certified button was added to the Search ribbon, enabling Search result to only show Certified results.

Admin Center / Data Management Gateway

The following functionality is new for the Power BI Admin Center:

In addition, one aspect of functionality for the Admin Center has changed with the December 2013 update:

  • You cannot monitor system health information using the dashboard in Admin Center, or by using OData feed.

Other updates for December include the following:

Updates for November, 2013

Power Query

With this month’s updates, Power Query enables authorized business users to certify shared queries in the Power BI Data Catalogue as authorized or authoritative:

  • Only members of the Data Steward role can certify shared queries. Power BI administrators can use the Admin Center to add users to the Data Steward role.

  • Members of the Data Steward role can apply the Certified flag to when sharing queries, and can set (or clear) the Certified flag when editing a shared query’s metadata in Power Query.

  • In Power Query search results, and search preview fly-out, certified queries are decorated with a special icon – a ribbon: Certified Query icon. Users can filter search results so that only certified queries are returned.

    For more information, see Certify Queries.

    But wait, there’s more:

  • You can now easily view and manage (edit, duplicate, reference, merge, append, share, and delete) all your queries in an Excel workbook from a single place. For more information, see View and Manage Queries in a Workbook.

  • You can now import data from all the supported databases in Power Query, except Microsoft Access, by using native database queries. For more information, see Import Data from Database using Native Database Query.

  • You can now perform Merge and Append operations inline, that is, as a new step at the end of the current query, instead of as a new query, when you perform these operations inside the Query Editor.

  • You can now connect to Microsoft Azure Table Storage sources from within Power Query, see Connect to Microsoft Azure Table Storage.

  • In this release we have greatly improved the Import and Navigation experience for hierarchical data sources (such as Databases, Web pages, OData feeds, etc.). You can directly select a table and load it into your workbook. We’ve also added the ability to import multiple items from the same source in a single shot, and specify whether you want to land them in a new Excel sheet or in the Data Model.

  • You can now specify the Load Settings for each of your queries upfront, within the Query Editor experience, which will save you from having to modify them after the initial load and maintain different artifacts in your workbook. Also when you disable “Load to worksheet”, we no longer create a sheet placeholder for this query, since the query can now be accessed from within the Workbook Queries side pane.

  • You can now use the Query Editor ribbon to perform reshape operations. The ribbon has the same functionality as the query context menus.

Admin Center / Data Management Gateway

Updates for October, 2013

This month’s Power Query updates were provided in the Power BI Team blog. Feel free to check out the Power BI Team’s most recent blog, too.

Applies To: Power BI



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language