So helpful! 7 connections, added to Data Model in 58.3 seconds. Over 500,000 rows of data.

Hi Jon.. great video and clever solution for saving a lot of time loading tables into Power Query. Looking forward to studying the code further. Thanks for sharing. Thumbs up!!

Hi Jon. Thank you for the tutorial. It was a great explanación on how Power Queries work.

Thank you, Jon. Great help!

This is awesome!

Good job! 👏

great sir, I was looking for it.

Great stuff

Thank you!!!!

Super useful and well explained. Thanks Jon. 20 tables (some 40,000+ rows) not added to data table 52 seconds.

@ExcelCampus

8 ай бұрын

Thanks! 😀

Jon thank you so much! You have saved me weeks lost from my life!

@mtstans

2 жыл бұрын

Added your script to my web scraper and ran 239 connections in 48.8 seconds. 22,448 cells total (appended) So sick man!

Hi Jon, great videos. Could you please organise a playlist for users at a beginner/intermediate level with all the videos about all the basic stuff and knowledge a beginner/intermediate should watch first? Thank you, keep up the good work

@ExcelCampus

4 жыл бұрын

Great suggestion! We'll try to work on this soon. 😀

Wow!!! This tutorial is what I’ve been looking for, thanks to you sir. By the way, I have a question, I hope you don't mind. What If, I want to exclude one table from the rest of all the table. What code do I need to change or add? Once again thank you very much.

Hello Jon thank you so much for sharing your amazing skills.

@ExcelCampus

3 ай бұрын

You're welcome! 😀

Really nice...

Hi. Thanks million

Thanks Bro.... You made my day 😍😍😍

@ExcelCampus

10 ай бұрын

Glad to hear that @talhaakram3621 😀

Very good！I will t r y

Thanks a lot for the example, - as I was not able to find anything in relation! :) I was mostly interested in conncection.add2 option. The code looks almost the same as I was working on. In demo file I found some nice ideas which will need to test, however, the example/demo file actually does not work on my Excel 2016, - no error notification is produced... "0 connections have been created in xx sec", - even if I remove all connections before running macros. So I am quite pessimistic on my future tests :( P.S. I want to create simple pivot table via VBA, with Data Model as I need to count unique values (distinct count) in the projects.... The main problem, - source filename always changes and I cannot produce the proper connection string.

Hello Jon, great video!!! Could you please help me with a code that deletes all queries in a Workbook. Thank you very much.

Thanks for your video, but I couldn't find the macro in the link.

Hi Jon. How to increase speed load data by power query in vba? Thank you very much

Hi Jon, Very instructive video, thank you. I do have a question for a problem that I can't seem to find a solution. Maybe it is something simple for you and you might be able to help me. I have to retrieve data from an ODBC connection into an Excel tool based on userform parameters. The number of records can easily reach over 1M where the Excel sheet can't hold the recordset result. I am using ADODB recordset within my Excel VBA. I thought that maybe I could create a power query in the workbook from the recordset directly. For you information, if I create an ODBC via Data > Get Data and load to the query and data model, it takes over 3 x longer than my ADODB recordset to the ODBC DSN. Users might be ok to wait for 20 seconds, but definitely not 60 seconds each time they want to get data. I appreciate your help if you can. Thank you

Hi Jon.thanks for the demo.When I run the macro, I met some error “user-defined type not defined”.Could you help me to resolve the problem please? Thanks.

Hi Jon, thanks for this great piece of code. I want to create a large appended table with these new connections. Is there a way to automate this through either vba or PQ?

@LC-sp9ox

3 жыл бұрын

Hi tony, did you figure out how to do this?

Thanks! What if you wanted to insert multiple connections to a workbook? Is that possible? E.g. Connect to two DBs and for sheet one use DB1 and for sheet 2 use DB2? I'm struggling to find that option Thanks in advance :)

Hi Jon, great job. When is the seminar going to be conducted?

@ExcelCampus

4 жыл бұрын

Hi Atta, it will happen this week. Here's the link to register to the webinar excelcampus.easywebinar.live/registration-blueprint.

Can you do a video of the reverse of this? How to loop through a bunch of PQs, adding each one to a new table in a new sheet ….. super helpful though. Thank you!

could you pleas helping me for this problem --graphs disappear after a save or save As in my excel 2016--

Hi Jon, great demo and thanks for the code, which I will use extensively :) By the way: I noticed you stripped the data model chunk from the xlsm file you provided; I've tried to replicate it but couldn't get it to work properly: although the syntax is correct (I've compared my code to yours and to the output of the macro recorder), the query wouldn't be added to the data model. Even running again the code produced by the macro recorder doesn't work: no errors but the query stays "connection only". What am I missing? Thanks again! :-)

@ExcelCampus

4 жыл бұрын

Hi Enrico, Sorry about that. That was an old version of the file. I added the data model functionality later on. I just uploaded the latest version that contains the data model option. Here is the link to the post where you can download it. www.excelcampus.com/vba/power-query-connection-only-all-tables/ Thanks again and have a nice day! 🙂

@enricogalli3744

4 жыл бұрын

@@ExcelCampus Thanks a lot. There's still something wrong, which I can't quite point my finger on yet. Tables are added to the data model indeed, but it's the tables themselves, not the queries which point to each table. Those are still "connection only", and can't be manually refreshed. I'd like to be able to mimic the behaviour of "close and load to -> connection only (add to data model)", so that if I run further transformations inside my queries, I will find them in my data model. I'll do some further investigation, but you gave me a hell of a head start! Thanks again, have a nice day! BTW your channel is truly awesome :)

Hi Jon, I hope you are doing good. I work in VBA & I need some help with a unique problem, I want to extract data from a closed excel workbook in my Macro without opening it. Problem with opening workbook is my files are heavy >90 MB & I need to open and close 8 files of that size. So for saving time, I think power query could help extract data faster. Let me know your thoughts on this. Thanks!

Hi Jon. I am trying to get some data from Azure DB using just a select statement. Can you help me on this please??

Hi John. How to custom ribbon my macros like in your video?

hi, I don't see the file in your blog. Can you please link it here?

@ExcelCampus

Жыл бұрын

Hello Fatima, the file is currently zip you can find it under "Download the Excel File with Macro". You can find it here: www.excelcampus.com/vba/power-query- connection-only-all-tables

Thank you so much! It saves valuable time. I am getting an error when trying to add new connections: "A query ti the name 'Append1' already exists." - DEBUG: 'Add query wb.Queries.Add Name:=sName, _ Formula:="let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source" Please advise!

@Superdupertyty

Жыл бұрын

Did you already get an answer to this? This is the error I'm getting as well

This is super useful especially when I have over 200 worksheets to create connections. However I couldn't find the Macro code on the other link, would you be able to send the Macro code?

@stephanierempel1289

2 жыл бұрын

Did you find the Macro code? I could not find it either.

i saw you have a macro for getting unique values, care to make a video about it?

@ExcelCampus

4 жыл бұрын

Hi Mohamed, Sure thing! We actually a video on that macro. Here is the link. kzread.info/dash/bejne/paOKsamllKWTfdI.html I hope that helps. Thanks again and have a nice day! 🙂

Hi Jon, I am wondering how to use VBA code to delete the Power Query cpnnections and Queries~ I tried to record the Macro but it fails. Thanks a lot~

Hi Jon, I downloaded the excel file.. I deleted the template and move other sheet from other file.. its not functioning.. kindly correct my procedure..do I need to edit Marco code?

Hello Jon...we are expecting Containers that are carrying many parts and are arriving on various days to our plant..and i have a task of analyzing if quantity in In-Transit Containers, would be sufficient for production, if yes how long i can produce before expecting next container arrival...so we do this activity every day to ensure plant continues with production. if there is delayed arrival of containers ,we opt for Air freighting the parts to fill production gap...i have daily production rate, stock on hand (daily opening stock), Intransit container arrival date, qty....I would like to get your help...is there a way i can create dashboard through power query which can displays every part numbers and by when plant run out of parts considering above factors

Hi Jon, many thanks for the video. My process was much slower than yours. 32 connections are created in 256 seconds. Any idea why it is very slow? Thanks and regards.

@excelemployeeleavetracker1274

3 жыл бұрын

Try changing the source file from Excel files to CSB files... for some reason PQ works much faster with CSV files.

Hi Jon, when I try to run the code on Excel 2013 it gives me a compile error: User-defined type not defined and highlights the row Dim wq As Workbook. Any clue why it is happening? Thank you.

How to open pq connect all table

Can we have a one-on-one tutorial class on data mining and scraping

@ExcelCampus

4 жыл бұрын

Hi Uchenna, I'm sorry, we don't do one-on-one training at this time.

PQ can combine all your tables from all sheets automatically. You don't need a macro for that.

@vigneshvicky5595

4 жыл бұрын

how can we do so....?

I keep trying to download the workbooks to get the macro, unfortunately there are no modules in the file when I do so. Any suggestions?

Hi Jon, could you direct me to where I could find this Macro code? I am not finding it in the download. I thought it was just me but I see a few other comments asking the same thing? This is exactly what I need and I would really appreciate the code, thanks!

@stephanierempel1289

2 жыл бұрын

I tried just typing it based off the video but the whole code is not visible in certain sections.

@stephanierempel1289

2 жыл бұрын

Ah I found my answer! The link in description takes you to the wrong article. If you search the article on the Excel Campus Website with the same name as this video, you can find the download that includes the macro code.

how to open menu my macros

After running the macro, I tried to append, after appending, the close and load To is not showing . How do I view the appended files in the work book?

Is this for only ms Excel 2016 and above. I want to use in excel 13 but it doesn't work because queries property is no available

@carloszerpazerpa2693

2 жыл бұрын

Hola t Hola Tonmoy. Lograstes conseguir la solucion? Yo estoy tratando de hacer lo mismo pero no me aparece esa propiedad

Hi John...great video...is there a way to use macro to edit existing connection...means the box where we define data source should come up..so that I can update the new link. Kindly help Thanks and regards Kishore

@excelemployeeleavetracker1274

3 жыл бұрын

You should make a dynamic Path to change connections. If you search John's KZread Playlist you will find a video on this.

10 seconds - 9 connections

Full link as it is cut off in the description: www.excelcampus.com/vba/power-query-connection-only-all-tables

52 tables 5.3 seconds.

359 seconds to create 268 connections.

382 connections in 292 sec

4.2 seconds!