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.

Percentage transformation

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 usingGet & 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.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.