Showing results for 
Search instead for 
Did you mean: 
New Member

Collecting data from URLs in list

Hi all,


I am trying to download lots lof data from many websites. 


So in column "A" I have a list of websites (or more accurately a list of different pages on the same site with the same table stuctures) i want to pull data from. I can then use PowerQuery to download and transform the data exactly how I need, but then I want to go to the next website in my list and pull the data and transform it the exact same way. Much like if I was dragging down an equation to repeat it many times with different data. Is there a way I can get PowerQuery to reference a cell relative to where it is and then drag it or copy it hundreds of times? 


Thank you so much!

Community Support
Community Support

Hi @orenz ,


Here's an example of how to do this:

1.Open the Power Query Editor by selecting "Transform Data" from the Home tab in the Power BI Desktop ribbon.

2.Select the table that contains the list of websites in Column A.

3.Select the "Add Column" tab in the ribbon and click "Custom Column".

4.In the "Custom Column" dialog box, enter "Data" as the column name.

5.In the "Custom Column" formula bar, enter the following formula:

    url = [Column A],
    data = Web.Page(Web.Contents(url)),
    table = data{0}[Table]

This formula uses the Web.Contents function to download the data from the website in Column A, and then uses the Web.Page function to transform the data into a table. The formula then returns the table.


6.Click "OK" to create the new column.

This will create a new column called "Data" that contains the transformed data from each website in Column A. You can then use this column in your visualizations or calculations as needed.

Note that this approach assumes that each website in Column A has the same table structure. If the table structure is different for each website, you may need to modify the formula to handle each case separately.



Best Regards,

Stephen Tao


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           


So I think you are very close, I am able to pull the string with the url and it seems to recognize it as a website. 


however in the new column we added it just says error, how do i point it to the right data transform?


When i try and pull data for the first time from the website it looks like this:



I can even say that this is the exact transform I want, just done to all the websites in the list 

    Source = Web.Page(Web.Contents("......removed for privacy..........")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site Address", type text}, {"Property Owner", type text}, {"Mailing Address", type text}})
    #"Changed Type1"



Thank you so much for the help!

Solution Sage
Solution Sage

Yes. Start with a table of your url strings and then add a custom with Web.Contents for each.



Microsoft Employee

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors