Querying multiple URLs

[Charley]

I've built a query for one URL. But there are many URLs that have the same pattern. I'd like to maintain a Table of those URLs in Excel. And then, I want to launch a process that performs the same query for each URL in the list and writes the results of all the queries into a new Table.

How do I apply the same query to a list of multiple URLs?

Charley

 

[Charley]

As a fallback approach, I set up a parameter Table with a URL. When I changed the URL in the Table then pressed Ctrl+Alt+F5, the result from my query did update as expected...though "hoped" might be more accurate.

I assume I now can write a short macro that loops thru my list of URLs, writes each to the parameter Table, refreshes, then copies the current results to another Table that accumulates all the results.

This is kinda kludgey, but it's better than being DIW (dead in the water).

Is there a more elegant solution?

Charley

 

[Bob Phillips]

Is this a Power Query query? If so, I would think you could built a tab le of URLs in Excel, read that table in, and execute each in PQ appending as you go.

 

[Charley]

Bob...As I say, I'm new to Power Query...essentially this week. What would be the key terms I would search for to find instructions on how to do that?

 

[Charley]

Bob...I received an email with your note, but I don't see it here. Thanks for your help!

 

[NormS]

Sounds like Bob may have answered your question but I would first create a function from your query with URL as a parameter. Load your table of URLs into PQ, then add a custom column with the function operating on the URLs. This should produce a column of table objects. Expanding this column will append all of the tables together. The ability to do this really turned me on to Power Query.

Norm

 

[Charley]

Norm,

Bob said he'd come up with an example. Perhaps your suggestion is what he's talking about. Because I've only been playing with PQ for a week, I only vaguely understand your suggestion. Also, because the file begins with about ten rows of meta data, followed by 1 to 8 rows of short notes that need to be merged, that complicates the whole thing.

So what I did over the weekend was to write some VBA that updates my parameter table with a new URL, refreshes the query, then copies the data and meta data into separate Tables. Last night, I ran it for 50 URLs and the process took about 3 seconds per URL.

I suspect that it's possible to query once, park the data, use it in one named query, then use the same data in another named query. But not knowing how to do that, I wrote two full queries, one for the data and one for the meta data. So the process isn't as efficient as it could be.

Charley

 

[AliGW]

It would be helpful if Bob would post here in this thread so that anybody can benefit from it. Lots of people are beginning to get into Power Query and Power BI.

 

[Bob Phillips]

Charly,

It is essentially what NormS said, but I will spell it out for you to try and make it clear.

In this example, I am just getting a number of closing stock prices. I started by running a Power Query web query for http://shareprices.com/lse/barc. I manipulated that data in the following ways to get what I wanted:
- when presented with a list of tables, I picked Table 0
- I deleted columns 1 and 2 as I did not want them
- I then transposed the data (a GUI option in the Transform tab) so as to get the data which was in rows into columns
- Again I remove the columns I don't want
- My prices had a letter p (for pence) at the end, so I replaced them with blanks
- The prices were text, so I cast them to numbers
- Finally I renamed the columns

This left me with the following query

let
    Source = Web.Page(Web.Contents("[URL]http://shareprices.com/lse/barc[/URL]")),
    Prices.Get = Source{0}[Data],
    Prices.ReduceTo = Table.RemoveColumns(Prices.Get,{"Column2", "Column1"}),
    Rows.ToColumns = Table.Transpose(Prices.ReduceTo),
    ClosingPrices.ReduceTo = Table.RemoveColumns(Rows.ToColumns,{"Column2", "Column3", "Column6"}),
    ClosingPrices.RemoveText = Table.ReplaceValue(ClosingPrices.ReduceTo,"p","",Replacer.ReplaceText,{"Column1", "Column4", "Column5"}),
    ClosingPrices.ToNumbers = Table.TransformColumnTypes(ClosingPrices.RemoveText,{{"Column1", type number}, {"Column4", type number}, {"Column5", type number}}),
    Columns.Rename = Table.RenameColumns(ClosingPrices.ToNumbers,{{"Column1", "Closing"}, {"Column4", "Offer"}, {"Column5", "Bid"}})
in
    Columns.Rename

which I then turned that into a function, by adding a function header with URL as a text argument, and I replaced the hard-coded URL in the query with the argument name. I named this function GetURL, and it then looked like

(URL as text) =>
let
    Source = Web.Page(Web.Contents(URL)),
    Prices.Get = Source{0}[Data],
    Prices.ReduceTo = Table.RemoveColumns(Prices.Get,{"Column2"}),
    Rows.ToColumns = Table.Transpose(Prices.ReduceTo),
    Subhead.Remove = Table.Skip(Rows.ToColumns,1),
    ClosingPrices.ReduceTo = Table.RemoveColumns(Subhead.Remove,{"Column2", "Column3", "Column6"}),
    ClosingPrices.RemoveText = Table.ReplaceValue(ClosingPrices.ReduceTo,"p","",Replacer.ReplaceText,{"Column1", "Column4", "Column5"}),
    ClosingPrices.ToNumbers = Table.TransformColumnTypes(ClosingPrices.RemoveText,{{"Column1", type number}, {"Column4", type number}, {"Column5", type number}}),
    Columns.Rename = Table.RenameColumns(ClosingPrices.ToNumbers,{{"Column1", "Closing"}, {"Column4", "Offer"}, {"Column5", "Bid"}})
in
    Columns.Rename

I then created a table in Excel of URLs and called it tblURLS. The data looked like this

URL
http://shareprices.com/lse/barc
http://shareprices.com/lse/mks

I imported that into Power Query, using the Data>Get & Transform>From Table ribbon option. With this query, I added a custom column with a simple call to my GetURL function, getURL(), and then expanded the table. I a...ens at each one, but you can for the queries.

 

[Charley]

Thanks, Bob! I'll dig into this next weekend (I hope). In the meantime, I'm off to the big city where a doc will repair (again) the damage I incurred 50 years ago during an argument with a horse.

 

[NormS]

My thanks, too, Bob, your example is very instructional. It looks like there's a typo in the Source statement of your GetURL function (the second code block); I believe the URL argument should appear without quotes, like this:

(URL as text) =>
let
Source = Web.Page(Web.Contents(URL)),

 

[Bob Phillips]

Thanks Norm, you are right of course. The dangers of cut and paste .

 

[Bob Phillips]

BTW, this is what the results table looks like in Power Query