Back
Excel

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

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

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

Top
34 comments
  1. I have downloaded the add-in file for Excel 2013 (Office Pro Plus) but I’m getting an error “Destination folder path cannot exceed 100 characters.”. I get this even when trying to install directly to C:\

    • Hi Mark,

      A few customers have reported this and we are investigating the root cause. As a workaround, can you try
      msiexec /i “PowerQuery.msi” WIXUI_DONTVALIDATEPATH=”1″

      Please let us know if this resolve the issue for you

      Thanks,
      Minh

      • Hi Minh,

        Thanks for your reply. That worked for me so thank you very much for your help.

        Now to have a play with the new features 🙂

        • Hi Minh,

          Can i know where i need to put the installer for this command to be used?
          i have the error that mentioned that “the installation package could not be opened” when run the command.

      • I too get that error, but that work around did not work for me. 🙁

        • Adam, this workaround didn’t work for me either at first. I was using Win+R key to run it (with the correct path to the .msi file). But when I launched a Command Prompt window, changed to the directory where the .msi file was, and ran the workaround from there, it did work. You might try that, if you haven’t already.

          • Awesome, that worked! Went in the Command Prompt and changed the directory as you said. Reran the command and it worked! Thanks!

  2. Dear Excel Team
    Have you fixed the issue with a broken connection to PowerPivot when changing the Power Query table?
    I know the workaround with copying the connection string but it would be really nice if this issue was fixed.
    Best regards
    D

    • Hi David,

      Can you please elaborate? Not sure I understand what the issue is.

      Guy
      Excel Team

  3. After installing – upgrading from Version: 2.30.4246 – I have queries failing with: “Object reference not set to an instance of an object.” All the queries call functions in Custom Columns.

    • Matt, could you please use the Send A Frown feature to send us more information about the error. We can then get back to you via email.

      Thanks,
      Sid

      • Hi Sid. Frown has been sent and I have received a reply from Minh Pham.
        Thanks for the support.
        Matt

        • Hi Matt – I am receiving the same errors. All my queries that use a function in a custom column are failing with the same “Object reference not set to an instance of an object”. Did you receive any information about a workaround or fix? Thank you.

          • Hi Chris. No work around provided. The response was: “Thanks for reporting the issue. It is a known issue and we have a fix read in our future release, stay tuned!”. We have stayed tuned but unfortunately it’s become such an issue that we have rolled back to the previous release. Have you found any fix? Thanks.

  4. “multiple tables within a single Append” thanks for that!!!!!

  5. Hi,

    I have a office 365 subscription and the latest excel edition installed. It appears that the latest Power Query updates are not included and I cannot update PQ manually.

    Is there any way to check which version of power query is (automatically) installed with Excel 2016 and to manually update it?

    Thank you!

  6. Hello,

    Is there a way to insert line breaks in your SQL code when querying via ODBC? In the previous versions Shift + Enter worked.

    Editing and creating queries will be VERY difficult if there is no way to line break in the newest version.

    Thanks!

    • Can you check whether just “Enter” works? You should be able to insert new lines this way.

  7. The April version of PQ appears to have some issues that prevents (in my case) the refreshing of SQL Azure DB connections. The error received is: [DataSource.Error] Microsoft SQL: VIEW DATABASE STATE permission denied in database ‘db_xxxx’.
    The user does not have permission to perform this action.

    The data loads fine in the preview but will not load into the data model. I have reverted to the previous version of PQ and the issue is resolved. I have sent a feedback email to the PQ team.

    • Thanks for reporting this, Ryan. We’ll follow up with you via Send A Frown email.

  8. H ithere. Thanks a lot for making Power Query stronger and stronger day by day… It’s a very useful piece of software, indeed. However, since the full power of it really lies in the M language, I’d like to ask when there will be anything even remotely resembling a decent M code editor… Thanks.

  9. Would anyone have a reason (or guess) why a previously updated O365 64 bit pro-plus edition install would lose these great updated features within the last week. I have been struggling with MS tech support to find someone who really understands the differences in these new features, or can explain the loss of functionality within “get and transform”as part of 2016, rather than the 2013 plus power query add-in.

      • Hi Guy,
        Thanks for the response. I have O365 2016 Pro plus 64 bit installed, but have been running comparison trials on different machines with everything from 2010 standalone 32 bit with the latest power query update (April 2016) , through to machines running O365 2016 ‘Home” 64 and 32 bit editions. Initially with the O365 2016 pro installs about a week ago, I did have all of the embedded “get and transform” functions and features that are described in the blog post above, but since (I think) an auto update for office was run on last Thursday, all of the “get and transform” features are generally greyed out , or have otherwise defaulted to what appears to be a very old edition of the power query. I have MS support ticket numbers to follow the development of the troubleshooting if you wish.

        • Hi Patrick,

          What is your Excel (under File -> Account -> Product Information -> About Excel) and Power Query versions (under Data -> New Query -> Query Options -> Diagnostics -> Version)?

          Guy
          – Excel Team

          • Excel is 2016 MSO 16.0.6001.1078 64 bit

            Power Query is Version
            2.24.4065.11301

        • Hi Patrick,

          According to your Excel and Power Query versions I can see that you are running on a Deferred Update Channel for Office 365. Customers running on this update channel are getting Office updates only few times a year.

          Please take a look here for an overview of different update channels for Office 365 and instruction on how to switch to a different update channel:
          https://technet.microsoft.com/en-us/library/mt455210.aspx

          Guy
          – Excel Team

          • Thanks Guy – that explains it exactly after testing.
            Machines and accounts tested with immediate update have all of the new features , machines and accounts on deferred updates do not.

Comments are closed.