VBA for me is tricky and sometimes hard, I prefer Power query for most of my tasks. 2:41 here you are actualy hardcoding the word "Sales" into the code, and thats fine if the user can do very basic M code changes, but for other users who dont know Mcode or power query this would be hard and they might stop using the solution, So to avaid this part what I do is to create a Designer's Table that has folder path / file path and filter phrase (in your example it would be "Sales" so the user can change it within excel worksheet without having to edit M code. 2:43 This solution again is most basic solution possible, and (sadly) as you mention few seconds later it adds whole bunch of step, tempfiles, functions etc. this means that for quite simple task of combining binery files PQ is overkilling it. What I do is dependable on the solution I work on. So there 2 simpler options: 1. Use Table.Combine( Table.TransformColumns(#"Filtered Rows1", {"Content", each Excel.Workbook(_)})) 2. Or use Table.Combine( Table.ReplaceValue(Step, each [Content], each Excel.Workbook([Content] ......))
@@miless2111sutube Sadly not all is doable with PQ, and even that Power query can connect directly into SAP, this had to be implemented by organization. and thus operating sap usualy is done manualy or via VBA. and yes it is called SAP SCripting. and then vba can get data and post data into sap.
@@ExcelInstructor VBA and PQ are just tools. Sometimes one is better than the other depending on the scenario. That said, I built a career out of SAP GUI Scripting in VBA so it will always be special to me.
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.
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
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👍
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 UA-cam vids. Thank you for your Effective and high quality content. Keep it on, stay healthy.
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
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...)
I'm the same, power query is responsible for everything data related, the data held separately and macros do the fancy visualisation tricks on charts. For me, that allows the user to see and amend (sometimes!) the data layer. If I can say to a client that all the fancy buttons and shapes do is hide columns or change selections or whatever and that the spreadsheet will still fundamentally work if the code "breaks" (in reality, it's almost always the user breaking something!), then they feel a lot more comfortable. It feels a lot less black box even though, in reality, power query is just as much a mystery to most users!
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.
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!
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?
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.
@@muhnuur Maybe Paul will help us understand how it works? but it works. Dim oc As Object, IsBG_Refresh As Boolean For Each oc In ThisWorkbook.Connections IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery oc.OLEDBConnection.BackgroundQuery = False oc.Refresh oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh Next
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.
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.
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.
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.
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.
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 watched a video that said the source data (the files you're referring to) aren't changed. That the changes are made in memory and just executed to create the new sheet.
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.
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.
@@nikolamilicevic5200 I found a big difference between 32 and 64bit excel when using power query and pivot. However, if you have the option to change the source data files (assuming your use case is similar to that in the video) from Excel to CSV, it is also much faster.
@@ricos1497 it was 64 and i couldnt (wouldnt like) change it to csv because it wouldnt be automated this way... But thanks for the advice.. maybe it was because the source files were on company network (shared drive)
@@nikolamilicevic5200 that could be it. I've had issues with security settings and things before that seemed to slow down files that were quite small, while other larger files posed no problems. It could of course just be a case of query optimisation being required of course. The best way to test, is to recreate (or copy if you're allowed) your data and query locally (C:/ drive) and run it there to see if it improves. Then convert the Excel files to CSV/txt and try again. That would highlight if the problem is with the data or the query (CSV or txt should be quick as a data source). In the past, I've written VBA to extract the data from files using adodb and save as txt/CSV. That way the VBA is kept to a relatively simple extract query, whilst power query does the transformation part. It just means that the user, in theory, could amend the transformation to add or remove columns or whatever quite easily and that any code is kept to a minimum.
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
Let me know in the comments if you prefer VBA or Power Query for this task
VBA for me is tricky and sometimes hard, I prefer Power query for most of my tasks.
2:41 here you are actualy hardcoding the word "Sales" into the code, and thats fine if the user can do very basic M code changes, but for other users who dont know Mcode or power query this would be hard and they might stop using the solution, So to avaid this part what I do is to create a Designer's Table that has folder path / file path and filter phrase (in your example it would be "Sales" so the user can change it within excel worksheet without having to edit M code.
2:43 This solution again is most basic solution possible, and (sadly) as you mention few seconds later it adds whole bunch of step, tempfiles, functions etc. this means that for quite simple task of combining binery files PQ is overkilling it. What I do is dependable on the solution I work on. So there 2 simpler options:
1. Use Table.Combine( Table.TransformColumns(#"Filtered Rows1", {"Content", each Excel.Workbook(_)}))
2. Or use Table.Combine( Table.ReplaceValue(Step, each [Content], each Excel.Workbook([Content] ......))
PQ seems to be one heck of a winner
@@miless2111sutube Sadly not all is doable with PQ, and even that Power query can connect directly into SAP, this had to be implemented by organization. and thus operating sap usualy is done manualy or via VBA.
and yes it is called SAP SCripting. and then vba can get data and post data into sap.
@@ExcelInstructor VBA and PQ are just tools. Sometimes one is better than the other depending on the scenario. That said, I built a career out of SAP GUI Scripting in VBA so it will always be special to me.
@@Cappurnikus Great, then maybe ytou could me out. how Do I grab a specific variant by name and not row position?
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.
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
Thanks. Glad you enjoyed it.
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👍
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 UA-cam vids. Thank you for your Effective and high quality content. Keep it on, stay healthy.
Thanks
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
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...)
Thanks Ruben. You make some great points.
I'm the same, power query is responsible for everything data related, the data held separately and macros do the fancy visualisation tricks on charts. For me, that allows the user to see and amend (sometimes!) the data layer. If I can say to a client that all the fancy buttons and shapes do is hide columns or change selections or whatever and that the spreadsheet will still fundamentally work if the code "breaks" (in reality, it's almost always the user breaking something!), then they feel a lot more comfortable. It feels a lot less black box even though, in reality, power query is just as much a mystery to most users!
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.
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!
Great video Paul. I just noticed how close PBI to PowerQuery within Excel.
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?
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.
I'm still fighting how to make macro knows when the data update is finished... 😔
@@muhnuur Maybe Paul will help us understand how it works? but it works.
Dim oc As Object, IsBG_Refresh As Boolean
For Each oc In ThisWorkbook.Connections
IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery
oc.OLEDBConnection.BackgroundQuery = False
oc.Refresh
oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh
Next
Thank @mdbm500, i'll try it.. 👍
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.
great job! could you please make files you use avilable so we could practice a at the same time?
Excellent video👍
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.
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.
Glad you liked it
Excellently and easily explained......
Thanks Madan
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.
Another excellent video. Thank you.
Thanks Tom
I prefer VBA, please make videos related to web scrapping with help of vba
Any example files to practice??
Great video!
If learning from now, Power Query. I think more useful moving forward with power bi and dataflows
Thanks for the feedback
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.
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)
The previous video is still available to watch ua-cam.com/video/mfG74mJr_8g/v-deo.html
How to add additional workbook?
Bitte mehr Power Query.
How can we do the reverse process?
How can we divide this file back to the previous situation?
I watched a video that said the source data (the files you're referring to) aren't changed. That the changes are made in memory and just executed to create the new sheet.
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.
What issues have you ran into with .xlsb files?
I will put it this way : for Data Power Query and VBA for Objects/UI things.
VBA + Power Query = Cool Native Excel Apps
In this case Power Query plus Power Pivot would be the best
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.
I like Power Query, like all tools it has its place. Why would I despise it:)
@@Excelmacromastery Because VBA is 100x faster and more flexible. although I must admit that PQ is walking in the park comparing VBA.
Vba with Ado, power querry is slow
Do you mean slow to run or that the Excel file is slower to open etc. because of PQ connections?
@@Excelmacromastery i stopped using it because it was pretty slow with refreshing the data later... Vba pretty faster and im not a wizzard like you
@@nikolamilicevic5200 I found a big difference between 32 and 64bit excel when using power query and pivot. However, if you have the option to change the source data files (assuming your use case is similar to that in the video) from Excel to CSV, it is also much faster.
@@ricos1497 it was 64 and i couldnt (wouldnt like) change it to csv because it wouldnt be automated this way... But thanks for the advice.. maybe it was because the source files were on company network (shared drive)
@@nikolamilicevic5200 that could be it. I've had issues with security settings and things before that seemed to slow down files that were quite small, while other larger files posed no problems. It could of course just be a case of query optimisation being required of course. The best way to test, is to recreate (or copy if you're allowed) your data and query locally (C:/ drive) and run it there to see if it improves. Then convert the Excel files to CSV/txt and try again. That would highlight if the problem is with the data or the query (CSV or txt should be quick as a data source). In the past, I've written VBA to extract the data from files using adodb and save as txt/CSV. That way the VBA is kept to a relatively simple extract query, whilst power query does the transformation part. It just means that the user, in theory, could amend the transformation to add or remove columns or whatever quite easily and that any code is kept to a minimum.
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