Forum Discussion

PbiCeo's avatar
PbiCeo
Icon for Helper II rankHelper II
6 years ago
Solved

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

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

General Comment
Need Help
  • dax's avatar
    dax
    6 years ago

    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.

1 Reply

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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.