INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

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

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

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

(OP)
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

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

Hi,

Don't have 2016.

Quote:

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.

CODE

'
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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/article/Get-Trans...

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

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

(OP)
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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Good thought. Thanks again. thumbsup2

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

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

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close