Power Query - Query the Web from a bunch of URLs stored in cells

HolyExcel

Board Regular
Joined
Jul 27, 2014
Messages
61
Hello Forum-members,

I'm having some hard time to achieve my goals for finishing a project where I need to query approx 500 URLs. Well, I could do things manually for each query, but maybe someone knows a way of repating things, as the queries only differ by the URLs...I would be very greatfull indeed if someone comes up with a solution.

In Detail:

In an Excel Table (or Spreadsheet) I entered a bunch of URL's. As mentioned: each Query from Web works just perfect with Power Query. But I can't figure out how to replace the fixed Url with the URL in a cell in advanced Editor. What I need: Everytime I change a URL in a cell it should exchange the URL in the webquery. Is there a possibility of replacing the URL as String form a cell? This would be part one of the Problem.

Second part: Does anyone know, is there a way to have PowerQuery repeating all things done in a query for each cell containing an URL? That would save me a bunch of time and I guess I'm not the only one interested in a solution here. Any solution tough is very much appreciated.

Thanks for helping me out or giving me a clear indication that it is not possible.
BR
 

ralliartur

New Member
Joined
Apr 15, 2015
Messages
30
If the query is the same for all URL-s then you can simply build a solution using two queries:

1. One for getting the data (example by my - downloading exchange rates)

Query1:
Code:
(filename as text) as table =>let
    Source = Xml.Tables(Web.Contents("http://www.nbp.pl/kursy/xml/" & filename & ".xml")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"numer_tabeli", type text}, {"data_notowania", type date}, {"data_publikacji", type date}, {"Attribute:typ", type text}, {"Attribute:uid", type text}}),
    #"Expand pozycja" = Table.ExpandTableColumn(#"Changed Type", "pozycja", {"nazwa_waluty", "przelicznik", "kod_waluty", "kurs_kupna", "kurs_sprzedazy"}, {"nazwa_waluty", "przelicznik", "kod_waluty", "kurs_kupna", "kurs_sprzedazy"})
in
    #"Expand pozycja"
2. For importing table with url-s and getting the data.

Query2:
Code:
let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("http://www.nbp.pl/kursy/xml/dir.txt "),null,null,1250)}),
    #"Added Custom" = Table.AddColumn(Source, "YEAR", each Number.ToText(20) & Text.Range([Column1], 5, 2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MONTH", each Text.Range([Column1], 7, 2)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "TABLE", each Text.Range([Column1], 0, 1)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([YEAR] = "2015") and ([MONTH] = "04") and ([TABLE] = "c")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "Custom", each Query1([Column1])),
    #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom3", "Custom", {"numer_tabeli", "data_notowania", "data_publikacji", "nazwa_waluty", "przelicznik", "kod_waluty", "kurs_kupna", "kurs_sprzedazy", "Attribute:typ", "Attribute:uid"}, {"numer_tabeli", "data_notowania", "data_publikacji", "nazwa_waluty", "przelicznik", "kod_waluty", "kurs_kupna", "kurs_sprzedazy", "Attribute:typ", "Attribute:uid"})
in
    #"Expand Custom"
 
Last edited:

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top