How to specify a table where a user can get link URL with Power Query

[PbiCeo]

Hello everyone,

I use web connector to get some tables on a site for example (https://www.wsj.com/news/types/the-americas). I want to get link URLs and titles as well (hopefully Column1 titles, Column2 their links).

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 the below Power Query but cound not specify the right table even through recommended table on PBI desktop.

------

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/

Is there any way to specify the right table?
Vladi

[dax · Accepted solution]

Hi @PbiCeo , 

You could try below M code 

let
    Source = 
     Web.BrowserContents("https://www.wsj.com/news/types/the-americas"),
    Links = 
     Html.Table(
      Source, 
      {{
       "Link", 
       "a[href^=""https://www.wsj.com/articles/""]", 
       each [Attributes][href]}}),
    #"Duplicated Column" = Table.DuplicateColumn(Links, "Link", "Link - Copy"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","https://www.wsj.com/articles/","",Replacer.ReplaceText,{"Link - Copy"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Link - Copy", each Text.BeforeDelimiter(_, "-", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Link - Copy", "name"}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Renamed Columns",{{"name", Text.Proper, type text}})
in
    #"Capitalized Each Word"

Best Regards,
Zoe Zhi

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