How to get titles and their links on a web site with Power Query

[PbiCeo]

Hello everyone,

I want to get titles and their links on a web site using web connector of PBI desktop.

(site url: https://www.wsj.com/news/types/the-americas)

Hopefully Column1: titles and Column2 their links.
I could get the below table from a recommended table.

Column1
AMLO’s ‘Hugs’ Won’t End Mexican Mayhem
Leahy’s Yankee Imperialism
Bolton’s Warmed-Over Venezuelan Dish
Cuban Medical Brigades to Mexico
The Culture Wars and the Street Wars
Will Canada Send Oil and Gas Packing?
Lockdown Puts Brazilian Lives at Risk
How Cuba’s Spies Keep Winning
Brazil Doubles Down on Reform
El Salvador’s President Is No Friend of the U.S.
AMLO Tries to Capitalize on Coronavirus
Audit the WHO’s Pan American Arm
No Gasoline in Venezuela
AMLO Defies Medicine and Economics
Repression in the Time of Coronavirus
Economic Flu Stalks Latin America
When Bernie Sanders Met Cuba’s American Hostage
Bernie Rewrites Cuban History
Mexico Slides Toward One-Man Rule
Trump’s Pitch to Puerto Rican Voters

I tried Power Qurery, but did not know how to specify the correct table.

----

let
Source =
Web.BrowserContents("https://www.wsj.com/news/types/the-americas"),
Links =
Html.Table(
Source,
{{
"Link",
"a[href^=""http""]",
each [Attributes][href]}})
in
Links

-----
https://blog.crossjoin.co.uk/2018/08/30/power-bi-extract-urls-web-page/

Could you help me with this?

Vladi

[camargos88 · Accepted solution]

Hi @PbiCeo ,

Try this m code:

let
Source = Web.BrowserContents("https://www.wsj.com/news/types/the-americas"),
Links = Html.Table(
Source,
{
{"Link","div:nth-child(n) > div > h3 > a", each [Attributes][href]}
},
[RowSelector="#latest-stories > article:nth-child(n)"]
)
in
Links

[v-juanli-msft · Accepted solution]

Hi @PbiCeo 

As tested, camargos88's answer can get links you need.

Then you could extract the title from the link as below:

let
Source = Web.BrowserContents("https://www.wsj.com/news/types/the-americas"),
Links = Html.Table(
Source,
{
{"Link","div:nth-child(n) > div > h3 > a", each [Attributes][href]}
},
[RowSelector="#latest-stories > article:nth-child(n)"]
),
    #"Inserted Text After Delimiter" = Table.AddColumn(Links, "Text After Delimiter", each Text.AfterDelimiter([Link], "/", 3), type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted Text After Delimiter", {{"Text After Delimiter", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","-"," ",Replacer.ReplaceText,{"Text After Delimiter"})
in
    #"Replaced Value"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88 · Accepted solution]

Hi @PbiCeo ,

Try this m code:

let
Source = Web.BrowserContents("https://www.wsj.com/news/types/the-americas"),
Links = Html.Table(
Source,
{
{"Link","div:nth-child(n) > div > h3 > a", each [Attributes][href]}
},
[RowSelector="#latest-stories > article:nth-child(n)"]
)
in
Links