Power Query question

[BamBamMoneyBags]

This may be a silly question.

I have queries added to my workbook all from the same URL.

Currently, my code loops through each query and extracts each table one at a time.

So it's connecting to the URL several times to get all of the tables.

I was wondering if there was a way for me to extract all of the tables with just one connection to the URL.

I'd rather not have to connect to the URL several times just to get the tables that I want.

Here's the code for what I'm currently doing:

Please Login or Register  to view this content.

Is it possible to import all of this in one go without having to connect more than once?

Thanks.

[teylyn]

That does not look like Power Query at all.

[BamBamMoneyBags]

Why do you say that? This code imports the Query Table made via Get and Transform (Power Query)

[BamBamMoneyBags]

To reiterate I'm wondering if it's possible to import all of the tables that are from the same URL with one connection rather than having to connect to the URL once for each table.

[xlnitwit]

Hi,

Unless you can show the M query you are using in Power Query, I think it will be difficult to assist. Adjusting the VBA code will not reduce the number of calls to your connection.

[teylyn]

If you use Power Query you handle the queries with M instead of VBA. Seeing the M code would be important.

[BamBamMoneyBags]

Thanks for the replies, I believe this is what you mean by M code?

Please Login or Register  to view this content.

I have a table known as "ParameterTable" that stores the URL. I commented out the old Source line.

Data16 refers to the current table, but there's other tables that I want to add.

How would I make this code grab and import additional tables all at once?

I appreciate the help.

[BamBamMoneyBags]

Would it be easier if I listed all of the tables I need?

Is the only way to do this by Appending the tables?

[MarvinP]

Hi BamBam,

I think you need to start learning the "M" language and install the Power Query Add-In. Then read:
http://www.mattmasson.com/2014/11/co...n-power-query/

Install PQ from
https://www.microsoft.com/en-us/down...8-478807aff212

[BamBamMoneyBags]

Thanks for the links Marvin.

I ended up appending the tables, but now I have a minor issue that I can fix via vba, but I was wondering if I could do it via pqfl.

When I append the tables, it merges columns that have identical header names. For example, there is only one "Player" header, which contains player names from all of the tables that have the "Player" header.

I was wondering if there was a way to append the tables without merging identical headers.

Here's the code thus far:

Please Login or Register  to view this content.

To re-iterate, I don't want the tables to share any columns at all, even if they have the same column name.

[teylyn]

If you you don't want columns with the same name merged, then you need to make sure that there are NO columns with the same name.

An append operation is typically used to append data from different tables that have the same structure, i.e. the same columns and column names. Power Query will append the rows of one table below the rows of the other table(s). The table columns don't have to be in the same order, since Power Query will figure out which column is which by the table name. If a table has a column name that is not in any other table, Power Query will add that column to the final result table.

[BamBamMoneyBags]

Thanks for the tip teylyn, everything worked out.

Thanks to everyone that helped out.

[BamBamMoneyBags]

Post Edited...

[BamBamMoneyBags]

Hello again, the issue I'm having now is that the code is too slow.

I'm not importing a lot of data, but it's still taking much longer to connect to the datasource and import my tables than it should be.

URL: http://www.sports-reference.com/cfb/...2-26-duke.html

PQFL code below.

Please Login or Register  to view this content.

All of the query's that are being appended do nothing other than extract one table.

This is the code I'm using to refresh the query:

Please Login or Register  to view this content.

I have Fast Data Load activated for every query.

Is there something extra I can do to speed up the process?

Sometimes it takes a minute to import my tables.

[xlnitwit]

Is there a particular reason for adding the tables afresh each time rather than merely refreshing the existing queries?

[BamBamMoneyBags]

nitwit, thanks for the response.

You're right it wasn't necessary to keep re-adding the tables, so I changed it via the following:

Please Login or Register  to view this content.

Unfortunately, this didn't change the speed at which the data was retrieved.

I feel as if there's a minor option that I'm overlooking that's making it take so long because it shouldn't take this long to import the data.

It gets stuck on "Connecting to datasource..." for far too long before finally importing.

[xlnitwit]

Is it possible for you to post the workbook for review?