Let me know in the comments if you prefer VBA or Power Query for this task

Excellent and informative video, as always. Thanks for posting. As I PQ newbie but a relatively experienced VBA developer, I think PQ is a fantastic addition to a programmer's or analyst's toolkit. It can perform a series of complex transformations with just a few clicks, which is simply amazing. Currently, as far as I know, PQ doesn't support Regex so pattern matching is quite challenging and might be better to hand off to VBA post loading the data into an Excel table. Also, the PQ Trim operation only removes leading and trailing spaces, not excess internal ones. In this regard Excel's built-in TRIM() function is superior since it can remove excess internal spaces from text as well. One aspect of PQ that is a game changer is its ability to completely replace VLOOKUP(). Using the Merge Queries option in the PQ editor, it is possible to accomplish the same functionality as VLOOKUP() and XLOOKUP(), I believe. This obviates the need for those functions, which means shorter loading times for workbooks and easier maintenance as data is added, removed, or edited to the associated tables. PQ's ability to effortlessly extract data from websites is simply astounding. I know you have covered how to do this in VBA, which is fantastic. But I think using PQ for web scraping tasks would be preferable under most circumstances. PQ + VBA working together can provide extremely powerful data cleansing and shaping functionality. I hope you will add more videos on the subject in the future, especially the M language, which I have dabbled in only slightly. There is so much to it that I honestly don't know where to start. For me, it's not a question of one or the other but rather how to use each tool to maximum advantage to perform ETL workflows. Thank you kindly.

PQ wins 🏆 any time, any day on this and many other repetitive tasks. No need reinventing the wheels. The beauty is the connection sources and less code writing except the refresh. I have been using PQ since 2020 and the journey has been awesome.

An outstanding video. If you would create a PQ Mastery course, I'd be the first one to buy it. If you can teach how to choose between VBA and PQ when solving problems, you'd provide a unique value. Even more valuable would be if you bring dynamic array and Lambda function into the mix. Excel is pumping out many powerful tools. The tricky part of solution building will become figuring out which of the tools deliver the best service.

I also switched from vba data imports to pq in 2019. Never regretted. Hower, I do not like M-Code from a coding experience, always repeating the previous step.... Another disadvantage is all the hard coded column names in your auto generatrd queries. Change a name somewhere and it crashes.... However, there are some smart ways around it, and I think PQ really shines, when you pull data from completly different sources and formats to combine them. Together with the datamodel, DAX and PowerPivot it's just great. therfore my usage of vba has been more and more limited. Anyway thanks a whole lot for your great channel and fantastic content. Keep going👍

Looks like PQ is the winner. Would to love to see more of this and get going on learning PQ as it is something I have not used before. See a lot of companies looking for this, so might have to start learning it sooner rather than later. Love the presentation style and the way you do things. Cheers

I beg you to start a series of videos on M language only (with no PQ interface, just M). You can do so much with M Query but surprisingly there are very few videos on this topic on yt.

Good to have people realise there are other tools apart from VBA that are usually much faster to implement for this kind of task especially for normal Excel users. It's worth noting that while the Power Query user interface is nice and simple, it provides only a fraction of what M-code can actually do, and this example despite being fast is really not the best way to achieve the ends. It's its own language, and just as with VBA, there is massive scope for efficiency in calculation time etc

Personaly i like PQ. After startet with vba i learned to Do the most parts faster and more easy to use with Power query and Power pivot. Thank you for your Videos, you teached me a lot im vba. I would love to learn more about Power query, Power pivot, Power bi with your KZfaq vids. Thank you for your Effective and high quality content. Keep it on, stay healthy.

Great video!

I've seen some comments about PQ being slow and I must say I disagree. I work with files that are close to a million rows and sometimes I combine some of those. Honestly, I don't think PQ is THAT slow, unless you want/need to sort the data. And yes, I know how to make fast macros, I'm suscribed to this channel ;) Same as you must learn how to write fast and efficient macros you must also learn how to improve your queries. Ever since I discovered PQ I stopped importing and cleaning using macros, gone are those days. Now I only use macros to whatever you can't do with PQ (applying formats, creating new files, adding/removing worksheets, returning information in only a column of an existing table...)

Definitely prefer PQ, but sometimes you need VBA. I had a project where the master Workbook created tables needed through PQ, and then a "Convert" Workbook pulled in just the tables needed, however doing that also pulled in the queries they needed which I didn't want. I was able to use VBA to delete all the queries which worked fine and didn't remove the tables - just made them static data. Suggestion for one of your great PQ/VBA videos!

Excellent video👍

Great video Paul. I just noticed how close PBI to PowerQuery within Excel.

This was great. There are horses for courses, in this instance PQ is the best way. I would love to see more videos using a lot files and also using a lot or rows and then compare run-times. I get the suspicion that with many rows and a few files is faster, and quite opposite with many small files with just a handful of rows on each one.

Another excellent video. Thank you.

Excellently and easily explained......

Thank you, I think this is a relevant topic. I do the main work in PQ. It is both faster and easier to write and there is a lot of work on formatting, aggregation, etc.. I also insert the update command in VBA. It took me a while to figure out how to make the macro wait until the end of the update so that the macro could continue working later. Colleagues for whom I do this work, it is clearer to see the navigation buttons, and formatted results, for this I use VBA.

great job! could you please make files you use avilable so we could practice a at the same time?

I like both PQ and VBA though I am not very good in either. I believe PQ can be beneficial if you know how to use it and would like to see more PQ content.

I prefer VBA, please make videos related to web scrapping with help of vba

Why do you format the columns (st the end of the video) in Excel, as opposed to in the PQ editor? On a separate note, would I be correct in saying whatever you choose to do in PQ (and Power BI), you can do using VBA (albeit more time consuming) but there are lots more things that can only be done in VBA that woule be impossible using PQ and Power BI?

If learning from now, Power Query. I think more useful moving forward with power bi and dataflows

I will put it this way : for Data Power Query and VBA for Objects/UI things. VBA + Power Query = Cool Native Excel Apps

I prefer VBA, but I think it's because I'm more used to doing it that way. In the other hand PQ democratize it. It is easier to learn PQ than to learn VBA first place.

Bitte mehr Power Query.

Thanks Paul If I close power query and load only in data model, how can I interact with the data in VBA? Thanks in advance. Pd: I don't know what you can give me with Power Query, M language, etc. How can I say that I prefer one or the other? Give us ALL.

How to add additional workbook?

Any example files to practice??

this is a nice demo, i guess the question would be, can this be recorded so you do not not repeat all these steps? sorry for the basic question. but this seem to be good when the data structure is consistent, I deal with data that changes in structure size and need to use the find function to locate my data.. so i think in this PQ might not help where with VBA i can write in the potential changes in data structure.

In this case Power Query plus Power Pivot would be the best

I think it would be better to show how to to stuff on VBA and Power Query on the same video. These way it would be easier to see the differences in case i missed the other video (which was my case)

I prefer vba because excel files with power query are problematic on different computers (calendaristic data is the major problem when we use power query)...and is also problematic when power query "drag" info from xlsb files.

How can we do the reverse process? How can we divide this file back to the previous situation?

I thought you despise Power query :). Therefore you have never done PQ video before. In my Company some say that VBA is used to refresh PQ. Which is often true, but to be honest Power query is great if your data have less than 10000 rows after that it's start to be very slow.

Vba with Ado, power querry is slow

PQ is too slow to refresh after combining multiple excel files with millions of data that are stored in a shared drive.

I ignored power query for years Saw no need for it Then the job required processing hundreds of thousands, even millions of records Power Query came to the rescue! 🦹‍♂🦸‍♂ I use a combination of both now Heavy lifting for PQ Automation for VBA Bigger toolbox never harms the jobs