Power Query Code Question

[RunTime91]

I am brand new to using PQ.

I have followed tutorials and created some queries, seems simple enough.

Here is my question. Is there a way to run these queries from a VBA UserForm, or from any source within Excel (similar to double clicking on a query in Access)

Thanks...

 

[RunTime91]

So I'm being told that MS BI applications (Power Query & Power Pivot) are little more than 'Gimmick Garbage'

and judging from the demonstrated lack of knowledge of these products on this board (considered by many the best Excel board on the net)

I am beginning to believe this may be true.

Is there anyone on this board that knows anything about these products and can give advice to a novice user

I am simply wanting to create queries and then be able to 'call' them with a click (or double click as in Access)

Am I wasting my time?

Any help at all would be great...

 

[Matt Allington]

So I'm being told that MS BI applications (Power Query & Power Pivot) are little more than 'Gimmick Garbage'

who told you that? The Tableau and/or microstrategy sales guy? This is simply not true.

and judging from the demonstrated lack of knowledge of these products on this board (considered by many the best Excel board on the net)

this is a completely different point. Microsoft has done a crap job of making these products visible to its core client base. The software is excellent.

I am simply wanting to create queries and then be able to 'call' them with a click (or double click as in Access)

Step back and tell me what you are trying to do, and why.

 

[Scotland The Brave]

https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

 

[jeremy_norbury]

I am brand new to using PQ.

I have followed tutorials and created some queries, seems simple enough.

Here is my question. Is there a way to run these queries from a VBA UserForm, or from any source within Excel (similar to double clicking on a query in Access)

Thanks...

Yes, you can ask the specific PQ table related to the userform to refresh itself - it's a single line, (e.g. something like Range("mytable").ListObject.TableObject.Refresh)

Anyone telling you that PQ isn't going to be the next big thing in Excel is lying or delusional or both.

 

[RunTime91]

Thank You to all who chimed in on this thread!

Matt, special thanks to you as you seem to be the sole resident expert in this forum ~ Thank You!

I am in agreement with those who believe MS BI software (PQ & PP) are well worth the time to master.

With that said, and to take a step back, my global vision with PQ is simply to write/record queries to pull data from both local and external sources.

Rework the data then use for reports and other databases. Pretty simple

Writing/recording queries seems fairly straight forward.

What is tripping me up is how do I call/use these queries?

I come from a VBA/UserForm environment, thus I am use to clicking buttons and getting results.

I would like PQ to work in the same fashion.

It seems you write queries in a specific workbook and your run your queries from the same specific workbook with the results appearing in the same workbook.

What if I want the results to appear in a different workbook or have the query open and appear in a new workbook?

Please remember I am brand new to world of BI, and thus my questions and likely my explanations will reflect my ignorance.

But please know I am serious user and I only come here when I truly need help; however, in this case you may need to help me to ask the right questions or provide logical explanations so you can help me.

I'm in if you are...

Thanks

 

[jeremy_norbury]

PQ isn't PUSH-centric, it's PULL centric. A table returned as a result of a query, continues to PULL data into itself.

The "different" workbook should reference the query in the original workbook (via PQ) and pull the data to itself...or simply reuse the query without storing the data locally.

There IS a refresh-all button and you CAN add simple VBA buttons to refresh individual tables if you want. Queries can also be configured (outside of PQ in data ->Connections->Connections) automatically upon load and every so many minutes.

 

[RunTime91]

Hey Jeremy ~ Thank You for responding back ~ Explanation understood (I think)

I get the push/pull concept so let's build from there.

If I understand your 'different' workbook explanation

it seems you are suggesting the way, or at least a way to use PQ is to establish a kind of 'Query Book'

And then have each of the source books (the different books) reference back to the query book to pull the data.

As far as the setting a timer to refresh a query, I have, for one of my queries, set that timer to refresh every 6 minutes. So I think I have that.

One of my needs with PQ is to go out to the corp. employee database (db A) and extract a subset of employees to backfill an employee database (db B) housed in a local business system.

If I understand correctly the method for doing this I would create the query in db B to go out to db A say twice a day and backfill the Emp. database in db B...is that correct?

Thanks again, Jeremy

 

[jeremy_norbury]

Hey Jeremy ~ Thank You for responding back ~ Explanation understood (I think)

I get the push/pull concept so let's build from there.

If I understand your 'different' workbook explanation

it seems you are suggesting the way, or at least a way to use PQ is to establish a kind of 'Query Book'

And then have each of the source books (the different books) reference back to the query book to pull the data.

As far as the setting a timer to refresh a query, I have, for one of my queries, set that timer to refresh every 6 minutes. So I think I have that.

One of my needs with PQ is to go out to the corp. employee database (db A) and extract a subset of employees to backfill an employee database (db B) housed in a local business system.

If I understand correctly the method for doing this I would create the query in db B to go out to db A say twice a day and backfill the Emp. database in db B...is that correct?

Thanks again, Jeremy

You can think of workbooks as being a source of data (other books or even tables/sheets or queries within THAT one) but not really a destination. THIS book has to be the destination (if there is one) of queries either here or elsewhere but they get executed here.

Your issue (if it's really an issue) is that you can't direct the output of a query INTO another workbook; you have to fetch the data here.

You could most certainly develop all the queries in a single workbook - with or without visualising that query as a table in the current workbook.

You then have the option, in another workbook, to either take data from the query (thus fresh data) or from a previously captured table (thus dated data).

You'd build a hierarchy of queries which eventually get sucked into a final workbook.

Now, when it comes to filling another database, PQ is not helping your problem because it has no features for exporting data. You'd still have the issue of using VBA to write to database B or eventually writing something inside Database B for sucking the data out of our new excel.