July 2016 Power Query Update

Hey folks,

I'm actually on vacation, so this post is going to be short.  I just wanted to make sure you all are aware that there is a new Power Query update available.

New features in the July 2016 Power Query update:

  • 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.

My Thoughts (without actually using it yet)

Now you can get full pictures at the official blog from Microsoft, but I'll just call out a couple that I think are pretty darned important from a usability perspective.

  1. Continuing with last month's update where we got Drag and Drop for the query groups, we now get Drag and Drop for the query steps.  That is just plain AWESOME.
  2. The new Advanced Filter dialog looks pretty good.
  3. The Date Pikcer also looks pretty helpful.
  4. A context menu to create new queries is also SUPER helpful.  One thing I'd like to see added here, is the ability to set each new query to load to connection/table/data model from INSIDE the query editor.  (Currently, the choice you make is applied to ALL new queries - the main reason I have my defaults set to load to connection only.)

10 thoughts on “July 2016 Power Query Update

  1. Yeah, but we don't have drag and drop on the Workbook Queries pane in Excel, just in PQ. A bad omission in my view.

  2. Really? I had actually downloaded (although not installed it) that day. The files were there, and still seem to be there at the download link today. I wonder if you have a cached page?

  3. Or just not there yet, Bob. 2 months ago we didn't have any drag and drop at all. Hopefully this is just a matter of time.

  4. Hey Sam,

    Yes, I'm seeing this as well. The version has incremented, but those two features are definitely missing... I'll ping the PQ team and see what's up.

  5. "Power BI Desktop has a slightly advanced version of Power Query, which is usually finds its way into the Power Query add-in for Excel 2013 and Excel 2016 Get & Transform within 1-2 months period." - ExcelTeam via Guy
    See com.:https://blogs.office.com/2016/07/25/july-2016-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in/

    Note the version info is curr. out of sync for a number of languages, incl. en-us.
    F.ex. the date just changed to 8/2/2016, but the version is still '2.35.4399.761'.

    /* -- Quick file-info check, for en-us -- */
    let
    Source = Web.Page(Web.Contents("https://www.microsoft.com/en-us/download/details.aspx?id=39379")),
    Data0 = Source{0}[Data]
    in
    Data0

    ----
    /* -- +just 4 fun...all language-files -- */
    let
    Source = Web.Page(Web.Contents("https://msdn.microsoft.com/en-us/library/aa751023.aspx")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Market", type text}, {"Language", type text}, {"Market Code", type text}, {"Market/Country Value", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Market/Country Value"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Columns1", each ([Market] "Hong Kong" and [Market] "Latin America" and [Market] "Singapore") and ([Market Code] "En-xa" and [Market Code] "es-us" and [Market Code] "nn-no")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows3", "Custom", each Web.Page(Web.Contents("https://www.microsoft.com/"&[Market Code]&"/download/details.aspx?id=39379"))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Source", "Data"}, {"Source", "Data"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Source] = "Table")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Source"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column2", "Column3"}, {"Column2", "Column3"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Data", each ([Column2] "Release Notes (English-only).docx31 KB"))
    in
    #"Filtered Rows2"

  6. hi Ken,

    i am looking for a way to write a formula in a conditional column output.

    is there a way to do that ?

    thanks a lot,

    Umut

    (sorry i've written to this update blog)

  7. Yes, it would require a code edit as you CA t input a working formula through the UI. Your options are to put it in as text and edit the M code or build it manually via the Add Custom Column dialog.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.