September 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce two new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

  • Enhanced Web connector—support for specifying a Command Timeout via the UX.
  • Enhanced CSV/Text connector—support for Fixed-Width delimited files.

Enhanced Web connector—support for specifying a Command Timeout via the UX

In this release, we enhanced the Web connector to allow users to specify a Command Timeout value from the connector UX dialog. Before this release, this could only be achieved by customizing the underlying M query.

In the Web connector, users can switch between Basic and Advanced modes. In the Advanced mode, users can now optionally specify a Command Timeout value.

sept-get-and-transform-1

Enhanced CSV/Text connector—support for Fixed-Width delimited files

Users can now easily import data from Fixed-width delimited CSV and Text files. With this update, users can now specify a list of positions to split by in the Preview dialog of the new CSV/Text import experience

sept-get-and-transform-2

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

Announcing the #ExcelWorldChamp Competition

Microsoft is excited to announce the Excel World Championship competition—#ExcelWorldChamp! Millions of people rely on Excel to get things done faster and make their lives easier. We want to meet all of you Excel fans and see what you can do.

The Excel World Championship participants will show their skills and creativity as they work through questions focusing on:

  • Data management
  • Data visualization
  • Formula writing

Excel World Champion featured imageFrom October to November 2016, Microsoft will run four rounds of Excel tests for residents of select countries. Only the top competitors in each round will make it through to the next level—until ultimately there is one Excel Champ from each country! Prizes for the country competitions vary. Residents of countries without a dedicated competition can compete in our open international competition.

In early 2017, the country champions and the international winner will compete against each other for the title of “Excel World Champ” to win a grand prize trip to Seattle, Washington, to meet with Excel product leads and help provide feedback for the next Excel features.

Round One begins October 3, 2016! For more details on the competition rules, eligible countries and registration, check out the Excel World Championship website.

—The Excel team

August 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce eight new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

  • Enhanced SAP HANA connector—allow multi-select of values for variables and parameters.
  • Enhanced OData connector—option to import Open Type columns from OData feeds.
  • Enhanced Access DB connector—new Select Related Tables button to “” in the Navigator dialog.
  • Option to generate Step Names in English within the Query Editor.
  • Description for Query Steps within the Query Editor.
  • Extract Week, Day or Month name from a Date/Time column from the Query Editor.
  • Merge Dates and Times into a Date/Time column from the Query Editor.
  • Extract Start/End of Hour from a Date/Time or Time column from the Query Editor.

Enhanced SAP HANA connector—allow multi-select of values for variables and parameters

With this update, users can now select more than one value for a given variable or parameter when leveraging the SAP HANA connector. This can be achieved via the Navigator dialog.

Enhanced OData connector—option to import Open Type columns from OData feeds

The OData connector now supports importing Open Type columns from OData feeds. Prior to this update, such columns were not supported in Power Query. This option is exposed under the Advanced Options section in the OData connector dialog.

August 2016 updates for Get Transform 1

Enhanced Access DB connector—new Select Related Tables button to “” in the Navigator dialog

We improved the Access Database connector to enable users to easily select related tables by adding the Select Related Tables button into the Navigator dialog. The behavior when clicking this button is the same as for all other databases that already support this capability—it selects all tables that have a direct relationship to any of the already selected tables.

August 2016 updates for Get Transform 2

Option to generate Step names in English within the Query Editor

When creating new Steps in the Query Editor based on transformations from the ribbon, the default Step Names will be based on the transformation name (i.e., SplitColumns, FilteredRows, etc.). These step names will be localized to the current installation language for Excel.

With this update, we introduced a new option to allow users to change this default behavior so that auto-generated step names use the English name for that transformation. This allows users to contribute to a single Excel report using multiple localized desktop versions, but keep the Step names recognizable by all parties (i.e., all in English by default). The new configuration setting is available under Data > New Query > Global > Regional Settings for non-English installations.

August 2016 updates for Get and Transform in Excel 2016 and the Power Query add-in 3

Description for Query Steps within the Query Editor

We also introduced a new Description field for Query Steps within the Query Editor, which gives the users an option to document and provide comments for their query transformations. The new Description field can be accessed from the right-click menu on any Query Step > Properties… command.

August 2016 updates for Get Transform 4

Extract Week, Day or Month name from a Date/Time column from the Query Editor

With this update, the users can now extract the Week, Day or Month names from a given Date/Time column. We’ve made this available from the Query Editor ribbon—either via the Transform tab, to modify an existing column, or via the Add Column tab, to insert a new one.

These two options can be found under Date > Day > Name of Day and Date > Month > Name of Month respectively. Note that the Day and Month names are localized according to the local setting for your current file (available under Data tab of the ribbon > New Query > Query Options > Current Workbook > Regional Settings for your Excel workbook).

August 2016 updates for Get Transform 5

Merge Dates and Times into a Date/Time column from the Query Editor

Another new transformation this month allows users to combine a Date column and a Time column into a single Date/Time column. This can be achieved by selecting those two columns and clicking Combine Date and Time under Date or Time menus in the Transform or Add Columns tabs respectively.

August 2016 updates for Get Transform 6

Extract Start/End of Hour from a Date/Time or Time column from the Query Editor

The last new data transformation this month allows users to easily extract the Start or End of an Hour based on a Date/Time or Time column. These two options can be found under the Time > Hour menu in the Transform and Add Column tabs.

August 2016 updates for Get Transform 7

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

Announcing the general availability of the Microsoft Excel API to expand the power of Office 365

Today, we are pleased to announce the general availability of the Microsoft Excel REST API for Office 365—now developers can use Excel to power custom apps. Excel is an indispensable productivity tool; users across all industries and roles embrace it. It is used for everything from simple task tracking and data management, to complex calculations and professional reporting. With our new interface, Excel for Office 365 can extend the value of your data, calculations, reporting and dashboards.

The Excel REST API release is a continuation of our journey to make Office an open platform for developers. The Office developer framework uses modern web development standards, so developers can build smarter apps that operate as part of Office on mobile, web and desktop platforms. The new Excel interface is exposed through the Microsoft Graph to access to intelligence and insights from the Microsoft cloud.

Here are a few scenarios how developers can use the new Excel REST API:

Excel as a calculation service

Users love the ease with which they can perform deep and complex calculations within Excel. Developers can now access Excel’s powerful calculation engine with instant results. For example, a mortgage calculator can take advantage of the PMT function from Excel—using a simple API call including principal, rate and number of payments. Excel does all the heavy lifting and returns the monthly payment instantly. With more than 300 Excel worksheet functions available, you have full access to the breadth of formula supported by Excel today. Complex business models don’t need to be rebuilt repeatedly; developers can leverage Excel to perform those calculations instantly and retrieve the results with simple API calls.

Excel as a reporting service

Excel is a reporting hero, from simple data tables to professional dashboards. Today, we are giving developers full access to all of Excel’s reporting features—making Excel an online reporting service within Office 365. Imagine any of the reporting scenarios users rely on today pulled into a custom app to create professional charts or analyze large sets of data intelligently, seamlessly blending Excel into those customized experiences.

Excel as a data service

Excel is also a great tool to store and track data. If your information is stored in a workbook, that data is available to any app integrating with Office 365, making its contents available to read from custom solutions and enabling them to use Excel as the data storage.

We look forward to working with developers and partners to discover and build new places and scenarios where Excel will continue to enable people to be more productive. Companies are already taking advantage of our new Excel REST API, including:

zapierZapier lets users easily automate tedious tasks. Zapier recently announced a new Excel integration, powered by Excel REST API, with near-infinite use cases, like simplifying a data collection process. Users can now build zaps where data is automatically added into Excel from other services, like emails and surveys, making Excel the data repository for all your connected services.

sageSage is working on integrating Sage 50 accounting software with Office 365, leveraging Excel via the new Excel REST API to access and combine business data in Sage 50 with the productivity benefits of Office. With powerful interactive Microsoft Excel reporting and business performance dashboards, Sage has simplified how to interact with the data and enabled small and medium businesses to make sense of data quickly—and make faster, better decisions.

Want to build custom apps using Excel?

Excellent! Visit dev.office.com/excel/rest, where you’ll find documentation and code samples to help you get started. It only takes a few lines of code to set up a basic integration with our Excel to-do list. Once you jump in, tell us what you think. Give us your feedback on the API and documentation through GitHub and Stack Overflow, or make new feature suggestions on UserVoice.

—The Office Extensibility team

July 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce 12 new data transformation and connectivity features that have been requested by many customers. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data connectivity and transformation features:

  • New SAP HANA connector.
  • New SharePoint Folder connector.
  • New Online Services connectors category.
  • Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
  • Improved Text/CSV connector, now exposing editable settings in the preview dialog.
  • Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
  • Data Source Settings enhancements, including “Change Source” capability.
  • Advanced Filter Rows dialog mode within the Query Editor.
  • Inline Input controls for Function invocation within the Query Editor.
  • Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
  • Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
  • New context menu entry to create new queries from the Queries pane within the Query Editor.

New SAP HANA connector

In this update, we added a new connector to allow users to import data from SAP HANA databases. The new connector can be found under Data > New Query > Database category, or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 1

New SharePoint Folder connector

Available under Data > New Query > File category, the new SharePoint Folder connector enables users to import data from multiple files within a SharePoint folder, similar to how the existing Folder connector enables users to import and combine multiple files from a filesystem folder.

July 2016 updates for Get Transform in Excel 2016 2

New Online Services connectors category

With this update, we added a new category that includes all available connectors for Online Services in Excel. The new category is available under Data > New Query > Online Services or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 3

Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2

In this release, we improved the IBM DB2 connector to provide users the choice to leverage the Microsoft driver for IBM DB2, which is also automatically included in the Excel/Power Query installation.

Within the IBM DB2 connector dialog, users can now select which driver to use under Advanced options.

July 2016 updates for Get Transform in Excel 2016 4

Improved Text/CSV connector, now exposing editable settings in the preview dialog

In this update, we improved the Text and CSV connectors so users can configure basic import settings from within the preview dialog. These import settings include:

  • File Origin.
  • Delimiter.
  • Detect Data Type (strategies include: Base on top 200 rows, Base on entire dataset or No data type detection).

Based on user choices, the preview in this dialog automatically updates. Users can then decide whether to directly load the data or edit first to apply additional data transformations.

July 2016 updates for Get Transform in Excel 2016 5

Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy

In this update, we enhanced the database connectors to expose an option that allows users to include Schema information as part of the Navigation hierarchy. This option is available under the Advanced option in all database connector dialogs, such as the SQL Server Database dialog below:

July 2016 updates for Get Transform in Excel 2016 6

Data Source Settings enhancements, including “Change Source” capability

Users can now easily update the location of a data source for all queries connected to that data source within a single Excel workbook. Before this update, users had to update the data source location for each of the queries connected to the source. The new solution allows users to do this in a single step.

Within the Data Source Settings dialog (Data > New Query > Data Source Settings), there is a new Current Workbook scope option, which allows users to limit the list of displayed data sources to only those that are used in the current file. From this view, the users can modify credentials, privacy levels and other settings for that data source, or use the Change Source option to modify the location for that data source.

July 2016 updates for Get Transform in Excel 2016 7

Advanced Filter Rows dialog mode within the Query Editor

We’re introducing a new mode in the Filter Rows dialog within the Query Editor. This new mode allows users to add multiple filter clauses within a single Filter Rows step (before this update, only 1–2 clauses were allowed) and also combine filter clauses based on multiple columns (before, all clauses were applied to a single column).

July 2016 updates for Get Transform in Excel 2016 8

Inline Input controls for Function invocation within the Query Editor

We improved the inline preview for functions within the Query Editor dialog to allow input values for function invocation. This new Enter Parameters section in the inline function preview is available for both “out of the box” functions (M Engine functions) as well as user-defined functions.

July 2016 updates for Get Transform in Excel 2016 9

Support for reordering Query Steps within the Query Editor by using drag and drop gestures

We now support reordering of Query Steps within the Query Editor by using drag and drop gestures. This can be done for each query via the Applied Steps section in the Query Editor.

July 2016 updates for Get Transform in Excel 2016 10

Date picker support for input Date values in Filter Rows and Conditional Columns dialogs

In this update, users can now leverage a Date picker control to provide input Date values in the Filter Rows and Conditional Columns dialogs.

July 2016 updates for Get Transform in Excel 2016 11

New context menu entry to create new queries from the Queries pane within the Query Editor

You can now add a new query in the Queries pane within the Query Editor when you right-click in the background of the pane or in a query group node. This allows a more intuitive experience for adding new queries when working in the Query Editor pane. Also, when you right-click a query group, the new query is added to the selected group.

July 2016 updates for Get Transform in Excel 2016 12

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

Faster OLAP PivotTables in Excel 2016

If you work with connections to OLAP servers, your PivotTables are now faster. The June 2016 update contains query and cache improvements to this powerful feature. You could benefit from these improvements, whether you use PivotTables to answer one-off questions or build complicated workbooks with dozens of PivotTables.

These updates are available to Excel 2016 users as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get these latest updates.

You’ll also need a workbook that has PivotTables connected to either the Data Model or to an OLAP server.

About the improvements

We have made significant improvements in three major areas while querying OLAP servers.

  • Improved query efficiency—Excel will query for subtotals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query and spend less time waiting for the results to transfer over your network connection.
  • Reduced the number of queries—Excel is smarter when refreshing your data. It now uses the LAST_SCHEMA_UPDATE property of a connection to limit schema requests to those times when it has, in fact, changed.
  • Smarter caches—When the schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries.

Getting the most from the improvements

No two workbooks are alike; some have received a bigger benefit than others.

It doesn’t matter if they’re connected to a tabular or multi-dimensional model. All of your PivotTables connected to Microsoft SQL Server Analysis Services, third-party OLAP providers or the Power Pivot Data Model will likely give you fresh data, faster.

Additionally, your pivots with subtotals and grand totals disabled, could be much faster to work with. Get insights more quickly when refreshing, expanding, collapsing and drilling into your data. The bigger the pivot you’re working with, the bigger the potential improvement.

To disable subtotals and grand totals, follow these simple steps:

  1. Under the PivotTable Tools section, select the Design tab.
    Faster OLAP PivotTables in Excel 2016 1
  2. Under the Subtotals button, select the Do Not Show Subtotals option.
    Faster OLAP PivotTables in Excel 2016 2
  3. Under the Grand Totals button, select the Off for Rows and Columns option.
    Faster OLAP PivotTables in Excel 2016 3

Try them yourself!

Try these new improvements and see how they can help you improve your analysis performance. Do you have ideas on other improvements that you’d like to see in Excel? Visit the Excel UserVoice and tell us about them.

—Alexander Lahuerta, program manager for the Excel team

Excel and big data

One of the great things about being on the Excel team is the opportunity to meet with a broad set of customers. In talking with Excel users, it’s obvious that significant confusion exists about what exactly is “big data.” Many customers are left on their own to make sense of a cacophony of acronyms, technologies, architectures, business models and vertical scenarios.

It is therefore unsurprising that some folks have come up with wildly different ways to define what “big data” means. We’ve heard from some folks who thought big data was working two thousand rows of data. And we’ve heard from vendors who claim to have been doing big data for decades and don’t see it as something new. The wide range of interpretations sometimes reminds us of the old parable of the blind men and an elephant, where a group of men touch an elephant to learn what it is. Each man feels a different part, but only one part, such as the tail or the tusk. They then compare notes and learn that they are in complete disagreement.

Defining big data

On the Excel team, we’ve taken pointers from analysts to define big data as data that includes any of the following:

  • High volume—Both in terms of data items and dimensionality.
  • High velocity—Arriving at a very high rate, with usually an assumption of low latency between data arrival and deriving value.
  • High variety—Embraces the ability for data shape and meaning to evolve over time.

And which requires:

  • Cost-effective processing—As we mentioned, many of the vendors claim they’ve been doing big data for decades. Technically this is accurate, however, many of these solutions rely on expensive scale-up machines with custom hardware and SAN storages underneath to get enough horsepower. The most promising aspect of big data is the innovation that allows a choice to trade off some aspects of a solution to gain unprecedented lower cost of building and deploying solutions.
  • Innovative types of analysis—Doing the same old analysis on more data is generally a good sign you’re doing scale-up and not big data.
  • Novel business value—Between this principle and the previous one, if a data set doesn’t really change how you do analysis or what you do with your analytic result, then it’s likely not big data.

At the same time, savvy technologists also realize sometimes their needs are best met with tried and trusted technologies. When they need to build a mission critical system that requires ACID transactions, a robust query language and enterprise-grade security, relational databases usually fit the bill quite well, especially as relational vendors advance their offerings to bring some of the benefits of new technologies to their existing customers. This calls for a more mature understanding of the needs and technologies to create the best fit.

Excel’s role in big data

There are a variety of different technology demands for dealing with big data: storage and infrastructure, capture and processing of data, ad-hoc and exploratory analysis, pre-built vertical solutions, and operational analytics baked into custom applications.

The sweet spot for Excel in the big data scenario categories is exploratory/ad hoc analysis. Here, business analysts want to use their favorite analysis tool against new data stores to get unprecedented richness of insight. They expect the tools to go beyond embracing the “volume, velocity and variety” aspects of big data by also allowing them to ask new types of questions they weren’t able to ask earlier: including more predictive and prescriptive experiences and the ability to include more unstructured data (like social feeds) as first-class input into their analytic workflow.

Broadly speaking, there are three patterns of using Excel with external data, each with its own set of dependencies and use cases. These can be combined together in a single workbook to meet appropriate needs.

Excel and big data 1

When working with big data, there are a number of technologies and techniques that can be applied to make these three patterns successful.

Import data into Excel

Many customers use a connection to bring external data into Excel as a refreshable snapshot. The advantage here is that it creates a self-contained document that can be used for working offline, but refreshed with new data when online. Since the data is contained in Excel, customers can also transform it to reflect their own personal context or analytics needs.

When importing big data into Excel, there are a few key challenges that need to be accounted for:

  • Querying big data—Data sources designed for big data, such as SaaS, HDFS and large relational sources, can sometimes require specialized tools. Thankfully, Excel has a solution: Power Query, which is built into Excel 2016 and available separately as a download for earlier versions. Power Query provides several modern sets of connectors for Excel customers, including connectors for relational, HDFS, SaaS (Dynamics CRM, SalesForce), etc. We’re constantly adding to this list and welcome your feedback on new connectors we should provide out of the box at our UserVoice.
  • Transforming data—Big data, like all data, is rarely perfectly clean. Power Query provides the ability to create a coherent, repeatable and auditable set of data transformation steps. By combining simple actions into a series of applied steps, you can create a reliably clean and transformed set of data to work with.

Excel and big data 2

  • Handling large data sources—Power Query is designed to only pull down the “head” of the data set to give you a live preview of the data that is fast and fluid, without requiring the entire set to be loaded into memory. Then you can work with the queries, filter down to just the subset of data you wish to work with, and import that.
  • Handling semi-structured data—A frequent need we see, especially in big data cases, is reading data that’s not as cleanly structured as traditional relational database data. It may be spread out across several files in a folder or very hierarchical in nature. Power Query provides elegant ways of treating both of these cases. All files in a folder can be processed as a unit in Power Query so you can write powerful transforms that work on groups (even filtered groups!) of files in a folder. In addition, several data stores as well as SaaS offerings embrace the JSON data format as a way of dealing with complex, nested and hierarchical data. Power Query has a built-in support for extracting structure out of JSON-formatted data, making it much easier to take advantage of this complex data within Excel.
  • Handling large volumes of data in Excel—Since Excel 2013, the “Data Model” feature in Excel has provided support for larger volumes of data than the 1M row limit per worksheet. Data Model also embraces the Tables, Columns, Relationships representation as first-class objects, as well as delivering pre-built commonly used business scenarios like year-over-year growth or working with organizational hierarchies. For several customers, the headroom Data Model is sufficient for dealing with their own large data volumes. In addition to the product documentation, several of our MVPs have provided great content on Power Pivot and the Data Model. Here are a couple of articles from Rob Collie and Chandoo.

Live query of an external source

Sometimes, either the sheer volume of data or the pattern of the analysis mean that importing all of the source data into Excel is either prohibitive or problematic (e.g., by creating data disclosure concerns).

Customers using OLAP PivotTables are already intimately familiar with the power of combining lightweight client side experiences in PivotTables and PivotCharts with scalable external engines. Interactively querying external sources with a business-friendly metadata layer in PivotTables allows users to explore and find useful aggregations and slices of data easily and rapidly.

One very simple way to create such an interactive query table external source with a large volume of data is to “upsize” a data model into a standalone SQL Server Analysis Services database. Once a user has created a data model, the process of turning it into a SQL Server Analysis Services cube is relatively straightforward for a BI professional, which in turn enables a centrally managed and controlled asset that can provide sophisticated security and data partitioning support.

As new technologies become available, look for more connectors that provide this level of interactivity with those external sources.

Export from an application to Excel

Due to the user familiarity of Excel, “Export to Excel” is a commonly requested feature in various applications. This typically creates a static export of a subset of data in the source application, typically exported for reporting purposes, free from the underlying business rules. As more applications are hosted in the browser, we’re adding new APIs that extend integration options with Excel Online.

Summary

We hope we were able to give you a set of patterns to help make discussions on big data more productive within your own teams. We’re constantly looking for better ways to help our customers make sense of the technology landscape and welcome your feedback!

—Ashvini Sharma and Charlie Ellis, program managers for the Excel team

June 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce four new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved features:

  • Conditional Columns.
  • Column type indicator in Query Editor preview column headers.
  • Reorder Queries and Query Groups inside Query Editor via drag and drop gestures.
  • Query Management menu in Query Editor.

Conditional Columns

With this update, we’re making it extremely easy for users to create new columns in their queries based on values from a different column. A typical use case for this includes creating a set of “buckets” or “categories” based on ranges from a continuous value column. For example, categorizing a person’s BMI into “Underweight,” “Normal,” “Overweight” or “Obese” based on well-known BMI value ranges, or defining categories such as “This Week,” “Last Week,” etc. based on a Date column.

Previously, this could be achieved by creating Custom Columns and capturing the conditional logic with a set of “if-then-else” statements. These expressions can very soon become extremely complex to author and maintain as new conditions are added.

Starting with this update, users can now define a set of rules and output values for the new column based on values in other columns within their tables. This can be achieved via the new Conditional Columns dialog, available in the Query Editor under the “Add Column” tab on the ribbon.

June 2016 updates for Get Transform in Excel 2016 1

Column type indicator in Query Editor preview column headers

With this update, we added column type indicators in column headers within the Query Editor preview. These new column type indicators allow users to quickly understand the types for each of the columns in their table, as well as change them to another type by clicking on these indicators or by using the previously available ribbon options (Data Type drop-down menu at the Home tab on the ribbon or Detect Type command under the Transform tab).

June 2016 updates for Get Transform in Excel 2016 2

Reorder Queries and Query Groups inside Query Editor via drag and drop gestures

Users can now easily reorder queries and query groups within the Queries pane inside Query Editor by selecting one or multiple objects and dragging and dropping them into the desired destination. This can be used to reorder items within a given group (or top level) and also to move objects into a query group.

Adding drag and drop gestures to the Queries pane inside Query Editor greatly improves the user experience for reorganizing queries and query groups, which was previously only possible via Context Menu options.

June 2016 updates for Get Transform in Excel 2016 3

Query Management menu in Query Editor

With this update, we’re exposing a new “Manage” drop-down menu for a query within the Query Editor Home tab on the ribbon. This menu exposes common management operations for a query, such as Delete, Duplicate and Reference.

June 2016 updates for Get Transform in Excel 2016 4

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce 10 new data transformation and connectivity features that have been requested by many customers.

These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data transformation features:

  • Remove blanks via Column Filter menu.
  • Convert Duration values to Years.
  • Keep Duplicates.
  • Hints for “sample input values” in the “Change Type with Locale” dialog.
  • Support for whitespace and line feeds in Query Editor preview.
  • Ability to disable previews from the Navigator window.
  • Technical name support in the Navigator window.
  • Rename queries directly from Queries pane in the Query Editor.

And the following new or improved data connectivity features:

  • Support for Command Timeout in the UX.
  • Set to disable Privacy Level prompts at machine level (including Registry Key).

Remove blanks via Column Filter menu

With this update, we have added a new data filtering option that will remove all rows where the value for the current column is null or empty. It can be accessed via the Column Filter menu drop-down.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 1

Convert Duration values to Years

This new transformation option can be found under the Transform or Add Column tabs. Within the Duration drop-down menu there is a new “Total Years” entry that allows you to calculate total years based on a Duration type column. The logic applied is to divide the total number of days by 365.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 2

Keep Duplicates

This new transformation allows users to keep only the rows with duplicated values on the select column(s). Before this update, only “Remove Duplicates” was available. This new option can be found on the ribbon under Home > Remove Duplicates split button and then select the Keep Duplicates command.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 3

Hints for “sample input values” in the “Change Type with Locale” dialog

Power Query allows users to change the type of a column taking into account the Locale or regional settings/formats in which the data is being represented. This can be done by setting the Locale value for the entire workbook (under Data > New Query > Query Options > Current Workbook > Data Load) and also can be customized for each specific “Change Type” step in the queries.

Within the “Change Type with Locale” dialog (accessible by right-clicking on a column header in the Query Editor preview then selecting Change Type > Using Locale…), users can now get a few “sample values” for the expected input format when selecting a specific data type and locale.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 4

Support for whitespace and line feeds in the Query Editor preview

With this update, we have added support for visualizing whitespaces in data cells within the Query Editor preview. This includes any whitespace characters, including line feeds.

You can toggle between “Show whitespace” (default behavior) and not showing it (old behavior) from the View tab on the Query Editor ribbon.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 5

Ability to disable previews from the Navigator window

With this update, we have added an option to disable previews in the Navigator dialog. This allows users to reduce the number of calls being made to the data source in order to retrieve these previews.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 6

Technical name support in the Navigator window

Some data sources support the notion of a “technical name” for objects within the source. These “technical names” are meant to provide a more meaningful name for the end user connecting to the data source than the “physical name” for the object. In this update, we added a new option to the Navigator dialog to allow users to switch between “physical name” (previous behavior) and “technical name” (new behavior).

Rename queries directly from the Queries pane in the Query Editor

With this update, it is now possible to rename queries directly from the Queries pane inside the Query Editor. To rename a query from this pane, simply select and right-click the query and select Rename, or double-click the query name, or select it and then press F2.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 7

Support for Command Timeout in the UX

With this update, users are now able to specify a Command Timeout value (in minutes) when connecting to database sources. This is a significant experience improvement since, before this update, this customization was only possible via custom formula authoring.

This new Command Timeout option can be found under the “Advanced options” section in data source dialogs.

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 8

Set to disable Privacy Level prompts at machine level (including Registry Key)

Power Query allows users to combine data from multiple data sources into a single workbook. When dealing with multiple data sources, it is possible for users to define queries that require sending data from one data source to another data source. To prevent accidental disclosure of private or enterprise data, Power Query provides a feature called “Privacy Levels.” This feature allows users to specify the Privacy Level (Public, Organizational or Private) for each data source that they connect to when trying to combine data from multiple sources. Based on the selected Privacy Level, Power Query will ensure that data from a Private source is not sent to any other sources and that data from an Organizational source is only sent to sources within the organization.

In some cases, this privacy enforcement can get in the way for users who want to define some data combinations that bypass this Privacy feature. Another potential effect of Privacy Levels is that, when combining data from multiple sources that are not supposed to send data between them due to their Privacy Levels, Power Query will compensate and download the data locally to perform the data combination in a secure way. This could result in a performance degradation due to the fact that data would have to be cached locally from all sources implied and combined in-memory. For those cases, users have the ability to ignore Privacy Levels as a “current workbook” setting.

However, given that this option was only available per file and per user, it would require a user to enable this option for each workbook they would like to leverage. In addition, ignoring this privacy protection needs to be approved by each user of the workbook, so someone opening a .XLSX file from a different user in their computer would have to either provide Privacy Levels for the data sources involved on the report or manually disable this feature in the Options dialog (under Query Options > Current Workbook > Privacy section).

In this release, we’re introducing a new setting to allow users and enterprises to pick one of the following behaviors:

  • Always combine data according to your Privacy Level settings for each source—This new option allows a user to “enforce” that Privacy Levels are taken into account for every .XLSX file on their machine, regardless of whether the “Current Workbook” Privacy Level setting is enabled or disabled.
  • Combine data according to each file’s Privacy Level settings—This is the default behavior and matches the Power Query behavior in previous releases.
  • Always ignore Privacy Level settings—This new option allows a user to always bypass Privacy Level settings for every .XLSX file in their machine, regardless of the Current Workbook setting.

These options are available to the user under the Query Options dialog:

May 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in 9

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

More April 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce more updates to this powerful feature set. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved data transformation features:

  • Ability to append multiple tables within a single Append operation.
  • Alphabetical sort of columns in Choose Columns builder.
  • Improved performance of renaming/removing/reordering columns.
  • One-click Percentage transform.

The following new or improved Data Connectivity features:

  • Support for jagged CSV files.
  • Exchange connector—OAuth support.
  • SharePoint List Connector—URL validation.
  • Database Connectors—ability to disable Navigation Columns in Query previews (performance optimization).
  • Web Connector—fine-grained scoping of Credentials.

And several other important improvements:

  • Virtualized preview in Query Editor.
  • Option to disable data previews to download in the background (performance optimization).

Ability to append multiple tables within a single Append operation

Users can now easily append more than two tables within a single Append operation. In the Append tables dialog, users can switch between “Two tables” and “Three or more tables” modes.

Two tables mode (previous behavior):

More April 2016 updates 1

Three or more tables mode:

More April 2016 updates 2

The new Three or more tables mode allows users to be much more efficient when trying to append several tables together. Before this option, users had to append tables one by one as different steps (or modify the underlying generated M formula).

While the Three or more tables mode is extremely powerful for advanced scenarios, we also want to ensure the simple scenario of appending two tables remains as easy as it used to be, so the default mode for the Append dialog is still Two tables. Users can switch between these two modes by using the radio-buttons at the bottom left corner of the Append dialog.

Alphabetical sort of columns in Choose Columns builder

The “Choose Columns” transformation builder has been improved to allow users to sort the list of available columns either alphabetically or based on natural order (previous behavior). The default sort continues to be Natural Order, that is, the order in which the columns appear in the table.

More April 2016 updates 3

Improved performance of renaming/removing/reordering columns

We improved the performance of common transformation operations within the Query Editor (Rename Columns, Remove Columns and Reorder Columns) to provide a better user experience. These operations are now performed locally, rather than requiring a reload of the data from the remote data source when applied.

One-click Percentage transform

Users can now easily calculate a percentage amount based on the value in a different column. In order to do this, users had to type Custom Column expressions in previous releases.

With this update, users can simply select the original column and then click Add Column > Standard > Percentage on the Query Editor ribbon. You are asked for the Percentage value to apply to the original column and then will get a new column as the output containing the result of this calculation.

Support for jagged CSV files

We have improved the CSV file connector to automatically detect jagged CSVs. Jagged CSV files are characterized by having a different number of columns across rows, generally with more columns showing up after the first few rows with less columns.

With this update, the detection logic for data columns in CSV files has been dramatically improved to recognize these extra columns. There’s no additional user action required to benefit from this improvement (besides importing the CSV file again using the latest product version).

More April 2016 updates 5

Exchange connector—OAuth support

We improved the Exchange connector and added the support for authenticating using a Microsoft account (organizational account) when using the Exchange connector.

More April 2016 updates 6

SharePoint List connector—URL validation

We’re improving the SharePoint List connector by performing validation of the URL provided by the user to connect to their SharePoint List.

Users can provide the URL to their SharePoint Site root and Excel will detect all SharePoint lists available within the site and present the user with a Navigator dialog displaying all available lists.

With this update, when users provide an invalid URL, they are notified about this issue and won’t be able to move forward. Before this update, users would get a subsequent error due to using an invalid URL, which was not indicative of the actual issue.

More April 2016 updates 7

Database connectors—ability to disable Navigation Columns in Query previews (performance optimization)

Users can now disable Navigation Columns when connecting to databases. This option allows a big performance optimization in retrieving data previews from databases when Navigation Columns are disabled.

Navigation Columns are extremely useful when trying to expand or aggregate fields from a related table in a database. For instance, from a Customers table that has relationships to an Orders table, users could expand fields from the Orders table to get the list of all Orders from all customers. Similarly, they could also perform aggregate operations over fields from the Orders table (such as the Sum of Total Order Prize). This would allow them to easily combine data from multiple related tables by just leveraging the existing relationships in the database and the Get & Transform UI options in Excel (Expand and Aggregate).

However, when not interested in this type of data transformation, it turns out that the cost of retrieving Navigation Columns for a given table is high enough that it degrades the performance of retrieving data previews, thus degrading the overall user experience for simple scenarios.

With this update, we introduced a new option in the Source dialog for database connectors to disable importing these relationships or navigational columns. Please note that this option is available for all relational database connectors except for SQL Server, Azure SQL Database and SQL Server Analysis Services.

More April 2016 updates 8

Web Connector—fine-grained scoping of Credentials

We improved the Credentials prompt when connecting to Web data sources to allow users to specify the scope to which they would like the credentials to apply. Users can now pick any of the levels within the URL (domain, specific page and all levels in between).

More April 2016 updates 9

Virtualized preview in Query Editor

We improved the Query Editor data preview so that it can display as many columns and rows as exist in the table. Before this update, there were a set of restrictions such as a maximum number of cells displayed in the preview (3,000) and a maximum number of columns (100).

With this update, we removed these limitations and users will be able to scroll in the table preview to see the entire set of contents.

Option to disable data previews to download in the background (performance optimization)

We added a new workbook-level option to disable downloading data previews in the background. Changing the default behavior would reduce the amount of data being processed in the background by Excel, which may be suitable for users who do not need to preview queries locally.

This new option can be found on the Data ribbon > New Query > Query Options > Data Load tab under the Current Workbook section.

More April 2016 updates 10

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

April 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce two updates to this powerful feature set. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

These updates include the following new or improved Data Connectivity features:

  • ODBC Connector—Support for selecting User/System DSNs
  • CSV Connector—Ability to specify Column Delimiter in the Source dialog

ODBC Connector—Support for selecting User/System DSNs

A common request from customers using the ODBC Connector was being able to select from a list of available DSNs on their machine when specifying the connection details in the Import from ODBC wizard in Power Query in Excel.

With this update, we added a new drop-down menu so users can select an existing DSN from the list of available User/System DSNs on their machine. In addition to selecting an existing DSN, users can specify additional Connection String details or select “None” in the DSN drop-down to specify a full Connection String independently from any existing DSNs (equivalent to the behavior prior to this update).

Get and Transform April 1

CSV Connector—Ability to specify Column Delimiter in the Source dialog

We improved the Source dialog for the CSV Connector so that users can customize the Column Delimiter option. Before this update, the Column Delimiter option could only be modified via editing the underlying M formula for the Source step.

Get and Transform April 2

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

Excel Online—what’s new in March 2016

Excel Online lets you view, edit and share your Excel workbooks from anywhere and it is free as part of Office Online or available for collaborating securely across your organization as part of an Office 365 subscription. We are pleased to announce several updates that help you with some of your most common spreadsheet tasks, including new ways to format data, use hyperlinks in your spreadsheet and explore data using PivotTables.

Read on for details about each one of these new and exciting improvements.

New ways to format data

Data comes in all shapes and forms. Excel Online now offers more number formats to display your data. To display the full list of format options, under the Home tab, click the Number Format drop-down and then select More number formats or right-click in a cell and select Format cells.

Excel Online march update 1

The beauty of using Excel Online is that it looks and feels like the Excel desktop experience you already know and love. Similarly, the Number Format dialog has the same options as the Excel desktop as we always try to keep the same and familiar user experience across all Excel platforms.

Excel Online march update 2

We know that currency formats are very common in spreadsheets, so we have made it easier for you to find the most common currency formats for your data. When you click the $ sign, under the Number Format section of the Home tab, you will find a list of the most common currencies with access to more accounting formats.

Excel Online march update 3

Connect your spreadsheet to more places using hyperlinks

You can now do more with hyperlinks in your spreadsheet when you are using Excel Online. For example, in addition to connecting a URL, you can now hyperlink to a place in the document or an email address. To display the Edit Hyperlink dialog, click Hyperlink under the Insert tab. Alternatively, right-click in the cell and select Hyperlink.

Excel Online march update 4

Excel Online march update 5

Excel Online march update 6

Summarize and group data using PivotTables

PivotTables (and Pivot Charts) are one of the most productive tools that Excel has to offer because they make it quick and easy to summarize and group your tables of data in any way you like. With this update, you can do more with your PivotTables with settings to change the way you summarize your value fields. If you would like to see the average sales amount instead of total sales amount, then the Value Field Settings is your dialog. You can launch the dialog from the Value menu in the PivotTable setting pane.

Excel Online march update 7

The Value Field Settings dialog consists of two tabs. The SUMMARIZE VALUE BY tab allows you to change the summarized value type.

Excel Online march update 8

Excel Online march update 9b

The SHOW VALUE AS tab enables you to change the type of calculation used in the PivotTable value fields. For example, instead of its absolute value, you can view the percentage out of the grand total.

Excel Online march update 10b

Excel Online march update 11b

If you don’t like the value name, or if you wish to shorten it, you can rename it in the Custom Name text box.

Faster filtering in PivotTables with search

Just like regular tables in your workbook, you can filter data in your PivotTable for quick analysis. Now, Search dialog includes a Filter dialog to help you easily find the values you want to display. You no longer need to scroll through a list of hundreds or thousands of values to find what you are looking for. Search as you type makes your experience fast and friendly.

Excel Online march update 12

Saving your last viewed workbook

When you save a workbook and then open it with Excel Online later on, we keep you on the same sheet you last viewed, making it faster for you to keep moving with your work.

On-premises availability

On-premises users will also be able to benefit from the improved experience that we’re building for the cloud. All you need to do is have SharePoint deployed and then integrate it with the upcoming release of Office Online Server. In the future, you can expect to see frequent updates coming to on-premises in parallel to being released to the cloud.

Try them out yourself

Try out these new features and see how they can help you do more with Excel from anywhere! Do you have ideas on other features and improvements that you’d like to see in Excel Online? Visit our Excel UserVoice and let us know what you think!