November Update of Power Query Preview is Now Available

November Update of Power Query Preview is Now Available

Rate This
  • Comments 9

We are very pleased to announce the November Update of Microsoft Power Query Preview for Excel, which you can download from this location. This is the most substantial update we’ve had in a while, and we are excited about bringing it to you – the theme on this blog a short while ago was Halloween, and we think of this as Christmas arriving early : )

The new features included in this month’s update range from new data sources to improved experiences and new capabilities for importing, reshaping, combining, managing or sharing your queries. Pretty much every product area has been improved this month, in one way or another. You can find more details below:

  • Connectivity to Windows Azure Table Storage: Windows Azure Table storage is a service for storing large sets of structured, non-relational data, in Windows Azure. You can now connect to tables in this storage directly from Power Query, and build queries using the same experiences for reshaping or combining them as for any other supported source. Learn how to connect to Windows Azure Table Storage here.

 Connectivity to Windows Azure Table Storage

  • Specifying a SQL native query command: In the last update we added the ability to specify native query commands in the Power Query Formula language functions that are used for connecting to relational databases (Sql.Database, etc.). This month we’re also making it easier to author such queries, as you can now specify a command text when connecting to a database using the Power Query UI – this will be handy for users who already have SQL queries that they would like to just use for imports via Power Query. Learn more about this feature here.

 Specifying a SQL native query command

  •  Improved navigator and multi-table import experience: 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. Note that import of relationships when building a data model is still a capability that we are working on enabling. That will be delivered in a subsequent update.

 Improved navigator and multi-table import experience

  • New Transformations Ribbon: We have added a ribbon to the query editor, to improve discoverability of many transformations that were previously “hidden” behind contextual menus (right-click menus on column headers, cells, etc.). This ribbon will let you apply operations on tables and columns based on the selection in the preview. Note that everything that was available via contextual menus is still there, so you can still use them if you want.

 New transformations ribbon

  •  Ability to specify Load Settings for your query upfront: As part of the Query Editor changes, we’ve also added a Query Settings pane which lets you manage the Name and Description for your query, the applied transformation steps and also the Load Settings. The advantage of controlling this before you load the query is that you can now disable load to worksheet upfront, and this will prevent new sheets from being added to your workbook if you don’t want them. Up until now, if you disabled “Load to worksheet” for your query you would still get a placeholder on the worksheet for your query (with a message saying “Load to worksheet is disabled”), this has also been improved so we no longer create this placeholder. You might now wonder how would you be able to re-enter your query if there isn’t a placeholder for it on the sheet… Continue reading the next new feature and you will find out how you can do this going forward.
  •  Workbook Queries pane: A single point to manage and access all the queries in the current workbook. This pane gives you the list of all queries in the current workbook, including previews, and provides easy access to the operations that can be performed on each query: Edit Query, Share, Merge, Append, Refresh, Delete, etc. Learn more about this feature here.

 Workbook queries pane

  • Inline Merge/Append: If you have used Power Query to merge or append different tables together, you know that you will end up with many intermediate queries and tables until you reach a final result. You can now keep adding Merge/Append steps to your query “inline”, if you do this from the Query Editor ribbon. The result will be just a new step at the end of the current query. Note that you can still get the same result as before, in a new query, if you access Merge or Append from outside the Query Editor (i.e. from the Power Query or contextual Query ribbon tab, or from the Workbook Queries side pane fly-out or contextual menus on each query).
  •  Certify queries that you have shared: We have added features to enable authorized business users and Data Stewards to certify shared queries in the Power BI Data Catalog as being authorized or authoritative:
    • Data Steward role: Power BI administrators can use the Admin Center to add users to the Data Steward role. Only members of this role can certify their shared queries.
    • Certify shared queries: Members of the Data Steward role can apply the “Certified” flag to queries when sharing them, and can set or clear the flag for shared queries when editing the shared query metadata in Power Query.
    • View certification status in Online Search: Certified queries will be decorated with a “ribbon” in the Power Query Online Search results side pane, and in the preview fly-out.

You can learn more about Certifying Shared Queries in here.

 Certifying queries you have shared

  •  Added support for 20 additional languages in this release (37 in total): In addition to the 17 languages that were already supported (see the list at the end of our previous update announcement here), we have added support this month for Bulgarian, Croatian, Czech, Estonian, Finnish, Greek, Hindi, Hungarian, Indonesian, Kazakh, Latvian, Lithuanian, Malay (Malaysia), Portuguese (Portugal), Romanian, Serbian (Latin), Slovak, Slovenian, Thai and Vietnamese.

This is all for now… As you can see we are working hard to improve Power Query every month and we really value your feedback about the experiences and capabilities in the product. Please don’t hesitate to reach out to us via Send Smile/Frown or using our forum with any questions, issues or suggestions that you may have.

Next steps:

Enjoy!

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • That is an impressive list of achievements, congratulations!

  • Oh my... This is simply great, features that I have been looking for!

  • Thanks guys! Just keep the feedback coming so we can continue adding useful features... : )

  • Is this for PC only or OSX as well?

  • Is there any support for pulling data from SSAS cube?

  • @R.Jones: This is PC only at this point.

    @Aviator: Currently not, but this is a frequent ask. We'll count your comment as a +1 for it.

    Thanks,

    M.

  • Version: 2.8.3476.202 breaks my queries... I have a query that consolidates data from mutiple worksheets/tables then uses some powerpivot relationships... Now they hang when I refresh. If I create a new powerquery using the same code (without the model relationships) the query completes... Any ideas...

    For example...

    let

       Sheets = {

    "Table17",

    "WP_DST_1",

    "WP_DST_2",

    "WP_DST_3",

    "WP_DST_4",

    "WP_DST_5",

    "WP_DST_6",

    "WP_DST_7",

    "WP_DST_8",

    "WP_DST_9",

    "WP_DST_10",

    "WP_DST_11"

    },

       Tables = List.Transform(Sheets, each Excel.CurrentWorkbook(){[Name=(_)]}[Content]),

       WP_DST_ALL = Table.Distinct(Table.Combine(Tables), {"Player", "Week"})

    in

       WP_DST_ALL

    Do I need to drop/add the relationship in powerpivot? Please say no...

    Regards,

    Charles

  • I cannot get this add-in to load into Excel 2013.  I tried every troubleshooting recommendation that I can find.

  • Hi Folks,

    I wanted to thank you for what you have done, honestly I am speechless!! bravo guys this tool is exactly what I dreamed of for many years : I implemented SAS financial management as well as SAS BI solution, Oracle Planning and  essbase: all this tools are great but none of them offer the flexibility that Excel offers to Analyst.

    bringing Cubes to Excel was a major advance, but bringing ETL to excel is an even greater achievement.

    I spent thousands of hours on data modeling, let's be honest Server class ETL is cool but will never (I say NEVER) replace desktop model as long as business use variety of data sources, most of companies uses different sources of information, and as analyst you can't wait IT integration. I used SAS enterprise guide as my desktop ETL for 10 years, now I switched to Power Query as the integration with Excel,power pivot and power view is so seamless!

    As a long date Data modeler I have some recommendations to share with you:

    some interesting features are missing: please create different join options (Cartesian product,left/right Outer/Inner joins) to the interface

    Add Append multiple tables to the interface

    Add Pivot column to the interface

    Add Custom filter options ( as group filters with and/or operators) to the interface.

    congratulation and thank you.

    regards.

    Toufik

Page 1 of 1 (9 items)