Released to Current Channel on May 4, 2016
New connection options
Get & Transform is now capable of extracting data from JSON and Azure SQL Data Warehouse sources. Extracting data with Get & Transform is fully explained in our Excel 2016 Expert Skills book.
Improved memory management
The main difference between the 32-bit and 64-bit versions of Excel is that the 32-bit version is only able to work with up to 2 gigabytes of memory, which can be a problem when working with large datasets. This update dramatically increases this limit to as much as 4Gb, although this may vary depending upon the computer's specification.
The 64-bit version of Excel 2016 does not have any limit on the amount of memory it can use, so this update has no effect on users of the 64-bit version.
Appending more than two tables using Get & Transform
Prior to this update, only two tables could be appended at once (although it was possible to append more tables by manually altering PQFL code). This update allows you to append three or more tables in a single operation.
Choose Columns sorting options
When transforming data using Get & Transform, you may be prompted with a Choose Columns dialog that lists all of the columns in the data. By default the columns are shown in the order in which they appear in the table, but this update allows you to choose to sort them alphabetically if you wish.
Improved transformation performance
The algorithms used to carry out transformations using Get & Transform have been improved, meaning that they will be significantly faster when working with large data sets.
A new transformation option has been added to the Get & Transform Query Editor, under Add Column > From Number > Standard > Percentage. This allows you to very quickly create a new column as a percentage of an existing column. You could do this using the Multiply option prior to this update.
Jagged CSV support
'Jagged' CSV files are comma-separated values files that have different numbers of columns on each row. This update enables Get & Transform to automatically detect and process a jagged CSV file if you import data from one.
Improved integration with Exchange and SharePoint
If you are using Excel with a Microsoft Exchange sever, you can now log in using a Microsoft Account. If you are using a Microsoft SharePoint server, you will find that some new validation rules have been added to prevent incorrect SharePoint URLs from being entered.
Web data source credentials
When connecting to a website to download data, you may need to provide credentials. Prior to this update you had to specify separate credentials for every online resource that you connected to. This update allows you to specify credentials for an entire website (or any part of a website), making it much easier to work with multiple data sources that reside on a single website.
Limits removed from Query Editor preview
Prior to this update, the Get & Transform Query Editor could only display a maximum of 3000 rows and 100 columns in its preview pane. This update removes these restrictions so there is no limit on the amount of data the Query Editor can display.
New data load options to reduce bandwidth usage
If you are using Get & Transform queries in your workbook, Excel will often download data in the background to allow it to be quickly previewed without having to perform a full refresh. Some users may wish to disable this feature, especially if they have limited bandwidth. This update allows you to disable background data loading using the command: Get & Transform > New Query > Query Options > Current Workbook > Data Load > Allow data preview to download in the background.