Collecting data from URLs in list

[orenz]

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!

[v-stephen-msft]

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:

let
    url = [Column A],
    data = Web.Page(Web.Contents(url)),
    table = data{0}[Table]
in
    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.           

[orenz]

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 

let
    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}})
in
    #"Changed Type1"

Thank you so much for the help!

[ppm1]

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

Pat