Excel 2016: "Get and Transform: New Query" vs "From Other Sources - MS Query" 2

[kjv1611]

On the Data tab in Excel 2016, has anyone noticed the "Get and Transform: New Query" and made use of it? Does anyone know whether that's the same thing with different wrapping as MS Query or something totally separate? Any thoughts on using one vs the other? One pain I've had with MS Query in the past is setting up a query, then the file gets moved for whatever reason, and the query no longer works.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hi,

Don't have 2016.

then the file gets moved for whatever reason, and the query no longer works.

What I do almost always is code the refresh in VBA.

I code the Connect String using ThisWorkbook.Path, so when the workbook changes location, nothing happens.

'
    sPath = ThisWorkbook.Path
    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
'.....
    With YourSheetObject.ListObjects(1).QueryTaable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Alright, I think I found my answer. "Get and Transform" in Excel 2016 is "Power Query" from before. Here's the Microsoft article about it for anyone else who wanted clarification as I did:

https://support.office.com/en-us/ar...cel-2016-881c63c6-37c5-4ca2-b616-59e18d75b4de

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Thanks. That's what I was thinking would have to be done, but just haven't had a reason. I had something that was used only temporarily at last company, and nothing so far at the new. I guess what would be good to do is to then add a button on the sheet for refreshes, and specify to only refresh from that button rather than the Data tab of the ribbon.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I'd use the Workbook_Open event or some other event to run the query refresh, rather than a button.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kjv1611]

Good thought. Thanks again.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

As in old excel external data queries, the query can be configured to update data when the workbook is opening. Any time user can right-click the output table and refresh data.

In new Get and Transform feature I found particularly useful:
- converting crosstab report to table,
- converting data types in tables,
- table aggregation incl. getting unique records,
- leaving queries as definitions, without output to workbook but still easy to edit, next they can be used as related in other query or as a source of pivot table,
- building a query as a series of commands rather than single sql,
- in case of joined tables they can be other queries too.

If you work with data and have Pro office version, it's worth to learn pover pivot feature, totally rearranging the way of working with pivot tables and introducing business intelligence in excel.



combo

 

[kjv1611]

Thanks for the thoughts, combo. I'm definitely planning on digging into the new Get and Transform feature (which according to MS is PowerPivot/Business Intelligence). I'll have to tread cautiously though. I remember issues between different versions of Excel at the last company with Power Pivot items. Not only that, even sometimes if someone emailed a PowerPivot file to someone else, they'd run into issues. It was liked in some ways, but to me, it sounded like more issues arose from using it than benefits. Could simply have been not making the best use of it, of course. I loved the looks of the reports that could be generated from it, but functionality was often painful.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57