power query vs VBA

[daveyc18]

learning PQ and see its power

however, it would be fair to say VBA might be better for data transformation if it needs to be done routinely?

let's suppose I get data every week that needs to be transformed to produce a chart

from what I understand, with PQ I would need to manually transform the data every time , and although doing it manually on excel would take longer, VBA code can automate this

so far it seems PQ might be better for more when you get ad hoc data and it needs to be cleaned up or for ad hoc requests when the data needs to look a certain way

 

[jdellasala]

learning PQ and see its power

however, it would be fair to say VBA might be better for data transformation if it needs to be done routinely?

let's suppose I get data every week that needs to be transformed to produce a chart

from what I understand, with PQ I would need to manually transform the data every time , and although doing it manually on excel would take longer, VBA code can automate this

so far it seems PQ might be better for more when you get ad hoc data and it needs to be cleaned up or for ad hoc requests when the data needs to look a certain way

There is no way that VBA is better for data transformation, ESPECIALLY if it needs to be done routinely!

Assume the query pulls in data from some external source, transformations are done, and the results are loaded to a table, and that needs to be done regularly - say daily or weekly as you said, and the source data is updated automatically. The query can be set to Refresh data when opening the file can be enabled, so all that would need to be done is for the file to be opened and updated. In fact, if by "ad hoc data" you mean one CSV file that you'll never get again, you're probably just better off opening it and fixing it. PQ is designed to be a one click solution for data import, transformation, and reporting, especially when the source data is either repetitive or being added to on a regular basis (as in Get Data from Folder).

That query can be loaded to a Pivot Chart AND Pivot Table (both required) instead of just a Table, and again, when the file is opened, the new data will be read by the query, and both the PT and PC will be updated. This is set in the Query's properties:

I am NOT a fan of VBA. It is antiquated, confusing, hard to track down where a macro lives (Workbook, Worksheet, Module), may need extra libraries loaded, and a myriad of other issues, but I do recognize it is a necessary evil. Be aware that Mr. Excel is an EXPERT in VBA, and way too many questions and answers revert to VBA when it is unnecessary or not even the right tool. And don't forget a Macro enabled Workbook is always a potential security problem.

The Power Query editor is MUCH easier to use, can do most routine data transformations without writing a SINGLE line of code, and fast. The one thing that takes getting used to is that it works with Columns, not cells, but the interface is so good it shows you the results instantly.

Take a look at this Power Query Tutorial and watch the YouTube video. You need to be more aware of its capabilities.

In short, the answer you NEED in your situation IS Power Query!

 

[daveyc18]

There is no way that VBA is better for data transformation, ESPECIALLY if it needs to be done routinely!

Assume the query pulls in data from some external source, transformations are done, and the results are loaded to a table, and that needs to be done regularly - say daily or weekly as you said, and the source data is updated automatically. The query can be set to Refresh data when opening the file can be enabled, so all that would need to be done is for the file to be opened and updated. In fact, if by "ad hoc data" you mean one CSV file that you'll never get again, you're probably just better off opening it and fixing it. PQ is designed to be a one click solution for data import, transformation, and reporting, especially when the source data is either repetitive or being added to on a regular basis (as in Get Data from Folder).

That query can be loaded to a Pivot Chart AND Pivot Table (both required) instead of just a Table, and again, when the file is opened, the new data will be read by the query, and both the PT and PC will be updated. This is set in the Query's properties:
View attachment 83296

I am NOT a fan of VBA. It is antiquated, confusing, hard to track down where a macro lives (Workbook, Worksheet, Module), may need extra libraries loaded, and a myriad of other issues, but I do recognize it is a necessary evil. Be aware that Mr. Excel is an EXPERT in VBA, and way too many questions and answers revert to VBA when it is unnecessary or not even the right tool. And don't forget a Macro enabled Workbook is always a potential security problem.

The Power Query editor is MUCH easier to use, can do most routine data transformations without writing a SINGLE line of code, and fast. The one thing that takes getting used to is that it works with Columns, not cells, but the interface is so good it shows you the results instantly.

Take a look at this Power Query Tutorial and watch the YouTube video. You need to be more aware of its capabilities.

In short, the answer you NEED in your situation IS Power Query!

I think one minor issue is that you still need to manually open the file from a drive ...whereas with VBA you can write code to open the file for u , no?

 

[jdellasala]

I think one minor issue is that you still need to manually open the file from a drive ...whereas with VBA you can write code to open the file for u , no?

NO.

You'd only need to open the Report file, which you'd have to do with VBA. That's what "Refresh data when opening the file" means! There are even ways to adjust the source file name easily if say, for instance the file name had a date imbedded in the filename like "Sales-YYYYMMDD". If files are in a specific folder, and a new file is added to weekly and you need a summary, just pull in all the files in that folder, and filter out the unneeded files (like *.doc or .CSV files that don't start with "sales" without having to do anything.

 

[GraH]

Since I started in 2016 spreading the PQ word at work, several experienced Excel users have told me they abandoned VBA all together after learning about PQ.
Our community leitmotiv was "With a ban on VBA we automate Excel by using standard features".

Obviously VBA covers a way wider scope, it is a programming language (and malicious code can do a lot of damage to your PC), thus it's providing more possibilities than M, the language of PQ that's oriented to mash-up data. Still many VBA solutions are on data mash-up, and thus could be replaced by Power Queries. If the VBA was well written, you will (almost with certainty) loose in performance. However PQ is at the base much easier to maintain.

Your example, is something I do with PQ and it's "programmed" with mouse clicks for +95%. The rest I tweaked over time by learning more on M and how to avoid hard coding, make it a bit more flexible and smart.

In the end it is much about the "developer": most will use whatever they know best, where they are most efficient at. PQ does enable even basic users to be independent of experts. I do not know a lot of them who are using VBA with great success.

 

[jdellasala]

Since I started in 2016 spreading the PQ word at work, several experienced Excel users have told me they abandoned VBA all together after learning about PQ.
Our community leitmotiv was "With a ban on VBA we automate Excel by using standard features".

Obviously VBA covers a way wider scope, it is a programming language (and malicious code can do a lot of damage to your PC), thus it's providing more possibilities than M, the language of PQ that's oriented to mash-up data. Still many VBA solutions are on data mash-up, and thus could be replaced by Power Queries. If the VBA was well written, you will (almost with certainty) loose in performance. However PQ is at the base much easier to maintain.

Your example, is something I do with PQ and it's "programmed" with mouse clicks for +95%. The rest I tweaked over time by learning more on M and how to avoid hard coding, make it a bit more flexible and smart.

In the end it is much about the "developer": most will use whatever they know best, where they are most efficient at. PQ does enable even basic users to be independent of experts. I do not know a lot of them who are using VBA with great success.

Thank you @GraH! As I said, there are certainly many things that can only be done with VBA, and I recognize that this is Mr. Excel's board, and he is a VBA Wizard, so it's not surprising to find a LOT of VBA questions.
Members that pose questions may think that VBA is the only tool powerful enough to perform a task, and sometimes there is a LOT of resistance to try a PQ solution. I understand that - learning PQ is not getting the job done. What I do NOT like is when well known members insist that VBA is THE solution for a problem that PQ is meant to solve.
While I am still not a VBA fan, I do find myself trying to learn how to solve problems that PQ is not appropriate for, and with the recent introduction of ChatGPT have found myself using it to find the solution to a problem that requires it.
That's one of the great things about the Forums - there's ALWAYS something to learn!

 

[GraH]

there's ALWAYS something to learn!

Amen to that!