none
VBA and Power Query in Excel 2016 Preview - Let's get started

    General discussion

  • It's time to try out Excel 2016 Preview and learn how we have integrated Power Query in the new Excel -

    • No longer an add-in
    • Accessible via the Data Ribbon
    • Excel Undo/Redo stack is not deleted
    • Copying query tables works better (new tables are refreshable)
    • Object Model is supported (VBA, C#, Powershell)

    This TechNet Gallery resource will help you to start writing VBA and automate your Power Query scenarios.

    The new VBA interface is simple:

    Set qry = ThisWorkbook.Queries.Add(queryName, M, queryDescription)

    You can use existing VBA code to create a workbook connection that is tied to the query, and load it to Data Model:

    ThisWorkbook.Connections.Add2 "Query1", _ "This is the workbook connection for query: " & qry.Name, _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & qry.Name _ , """" & qry.Name & """", 6, True, False

    Next step - Download the sample workbook from TechNet Gallery and get started.

    Looking forward for your feedbacks,

    Gil



    Wednesday, May 27, 2015 7:52 AM

All replies

  • Do you plan to support the ability to create query groups and add/remove queries from those groups via VBA? I think that's an important feature that's currently missing in the Preview.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, May 28, 2015 8:16 AM
  • Hi Chris,

    We don't plan to support grouping or query ordering in Excel 2016, but will consider this improvement in the future. Could you share more details about the user scenarios in which this missing functionality is essential?

    Thank you ,
    Gil

    Thursday, May 28, 2015 8:57 AM
  • For me (and for at least one other person I've talked to about this), one of the main uses of VBA for Power Query is for importing and exporting queries to/from different workbooks. If you have a library of useful queries stored in a master workbook, you might create a new workbook for a new task and then import some or all of your library from that master workbook. If you have a lot of queries then it's likely that they will be organised into groups, and you will want those groups to be imported too.

    Chris

     

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, May 28, 2015 9:12 AM
  • Chris,

    What you describe is a very common scenario for me. Currently, I maintain multiple text files that group related queries. I open a file, select one query at a time, and paste into a blank query in Power Query (I've put the Blank Query option is on the Quick Access Toolbar (QAT), because I use it so often). However, this process is a royal P.I.T.A.

    One shouldn't need VBA to accomplish copying queries from one workbook to another. You should be able to select either a group name, multiple query names (or both), right click, select Copy to... and select any other open workbook.

    Gil,

    I have a significant VBA project that converts data in CSV files from one data format to another. The conversion heavily depends on SQL queries (using lookup tables stored in worksheets). My hope is to replace SQL queries with Power Query queries. There is much logic that could potentially be shifted to PQ (e.g. determining which column in a lookup table to select based on the Excel user language (either French or English). There is other logic that Power Query can handle (e.g. validating the CSV file), but these scenarios can generate error conditions that can't be handled in VBA.

    In the end, I'll probably build out most of the conversion logic in Power Query, independently of VBA. VBA will be used to set parameters for Power Query to use (file path from a file dialog box (after file validation), language in use, additional data to fill in custom columns, and so on, followed by refreshing connections that use these parameters. However, this approach would not require any of the new objects.

    Thursday, May 28, 2015 1:48 PM
  • Chris, Colin, These are great feedbacks that will help us to shape the future experience post Excel 2016.

    Thursday, May 28, 2015 4:36 PM
  • Do you want to see Powershell scripts that use the new Object Model interface of Power Query?

    Check out on TechNet Galleries resource here. It demonstrates the copy of Power Query queries from one workbook to another via Powershell. There is also a script that deletes the queries.

    Note: This is only working on Excel 2016 (Preview).

    Tuesday, July 21, 2015 3:11 PM
  • Hi Gil
    Something Im working on at the moment, I've noticed that the PQ's I've written are actually quite slow and being able to identify the steps in the Query that are pretty painful would be a god send.

    in Excel 2013 (with a bit of help from another blog I've put this together to help me identify the problem PQ's)
    but it cant help me identify where in the steps the biggest problems are

    Public Sub UpdatePowerQueries()    
    ' Macro to update my Power Query script(s)
    Dim TStart As Date
    Dim TEnd As Date
    Dim cn As WorkbookConnection
    
    For Each cn In ThisWorkbook.Connections
    If Left(cn, 13) = "Power Query -" Then
                TStart = Now
                cn.Refresh    
                TEnd = Now    
                 Debug.Print cn + ": " + CStr(DateDiff("s", TStart, TEnd)) + " Seconds"    
                 Debug.Print "----"
    End If
    Next cn
    End Sub







    Thursday, July 30, 2015 8:55 AM
  • Hi Gil,

    Can you tell me whether a macro recorded in Excel 2016 that creates PQ connections could be saved and subsequently run inside Excel 2013?

    If not, can I assume that if I ran the above PQ creation macro in 2016, and then saved the resulting file to a non-macro-enabled xlsx file, will that file open and work correctly in Excel 2013?

    The reason for the enquiry, is that I need to dynamically create 30 connections in a spreadsheet, so a macro would seem the quickest approach, but all my end users of the spreadsheet only have Excel 2013 (with the PQ add-in installed).

    Many thanks for your advice,

    Geoff

    Friday, February 26, 2016 10:27 AM