Web.contents power query reference cell

Question

Please have a look at the following code

Source = *Web.Page(Web.Contents(""https://in.finance.yahoo.com/quote/20MICRONS.NS/history?p=20MICRONS.NS"")*)

For the italics text i.e. the hyperlink, I am trying to link it to a cell. Can anyone tell me how this can be done?

I tried using the Cells tag but in vain

Answer 1

I'm not certain what you are asking, but if you are saying that you want to replace "https://in.finance.yahoo.com/quote/20MICRONS.NS/history?p=20MICRONS.NS" with a URL from a column, and bring in that URL's info, you might try Web.Page(Web.Contents([Custom])), where [Custom] is the column with the URLs you want to use. This will embed a table of information for each URL listed in the "Custom" column, in that URL's corresponding row.

For instance, if the column "Custom" has https://in.finance.yahoo.com/quote/20MICRONS.NS/history?p=20MICRONS.NS in it, like this:

...then creating a new column with Web.Page(Web.Contents([Custom])) would embed this table:

...in the new column, like this:

Expanding the new column (in this case, "Custom1") would give you something like this:

And expanding the "Data" column would give you something like this:

I believe the info in "Column1.1" and "Column2" is what you are after from the URL you provided.

---

An edit following our comment discussion is below

Using Excel 2016, I set up a worksheet with the URL in column B, like you said yours is. Like this:

Then I click in either cell A1 or B1, and then I click the "Data" tab and the button to create a query from a table:

...which gives me this:

...and I click "OK", which gives me this in the Power Query Editor:

Then I click the "Add Column" tab and "Custom Column" button...

...and fill out the pop-up screen like this:

...and click "OK"...which gives me this:

Then I click the button at the top right of the "Custom" column......to expand the tables in the "Custom" column and get this:

Then I click the button at the top right of the "Data" column......to expand the tables in the "Data" column and get this:

Here's my query code:

let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Web.Page(Web.Contents([Column2]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Caption", "Source", "ClassName", "Id", "Data"}, {"Caption", "Source", "ClassName", "Id", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom", "Data", {"Column1", "Column2"}, {"Column1.1", "Column2.1"})
in
#"Expanded Data"