none
Extracting hyperlinks from webpage table RRS feed

  • Question

  • I am trying to create a query that will display the links that are in the second column of a web pages table. I know this has been asked in the past but please bare with me as this all very new to me. I looked an a earlier post and just became very confused. I using office 2016.

    I want to show the table on another affiliated webpage. 

    I'm doing that now with a query but the links display as text.  I understand that if I write the right syntax into advanced editor I can get what I want. I just have no idea what to do. 

    Here is the editor script now;

    let
        Source = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Data0 = Source{0}[Data],
        #"Changed Type1" = Table.TransformColumnTypes(Data0,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}})
    in
        #"Changed Type"

    The way this table displays data is a bit unorthodox with information about an event on two rows. I believe this was done so it could display without having to scroll horizontally. In column 2 there is links for email and row under that a website.  

    Please offer any advice you can to get this old man, (70+) looking good to his friends. Thanks

    Robert

    Saturday, July 29, 2017 1:56 AM

Answers

  • Hi Robert,

    please try this code:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterBorder = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER")),
        #"Filtered Rows" = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "http")),
        Websites = Table.AddColumn(#"Filtered Rows", "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        #"Removed Columns" = Table.RemoveColumns(Websites,{"Column1"}),
        IndexedWebsites = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 2),
        Custom1 = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "mailto")),
        Custom2 = Table.AddColumn(Custom1, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""mailto:", """")),
        #"Trimmed Text" = Table.TransformColumns(Custom2,{{"Custom", Text.Trim}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Trimmed Text",{"Column1"}),
        IndexedEmails = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 2),
        SitesAndEmails = Table.Combine({IndexedEmails, IndexedWebsites}),
    
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},SitesAndEmails,{"Index"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Custom"}, {"ClubConnections"}),
        #"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
    in
        #"Removed Columns2"

    The last column "ClubConnections" will now contain the email & webpages.

    As you might have noticed, the website doesn't return the desired data nicely, so a bit of wrangling is required here unfortunately.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, August 2, 2017 6:40 PM
    Moderator
  • Sorry, lost the clicking out of sight.

    This is a bit tricky. In Excel, you can apply the "HYPERLINK"-function to do this. So you either add a column in Excel to your table to do just this, or you modify the query to return the full Excel-function. All that's left to do then in Excel is to check the column and replace ' by nothing. This will "activate" the Excel-formula and your links will become clickable:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterBorder = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER")),
        #"Filtered Rows" = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")),
        Websites = Table.AddColumn(#"Filtered Rows", "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        #"Removed Columns" = Table.RemoveColumns(Websites,{"Column1"}),
        IndexedWebsites = Table.AddIndexColumn(#"Removed Columns", "Index", 0,1),
        #"Replaced Value" = Table.TransformColumns(IndexedWebsites, {{"Custom", each "'=HYPERLINK("""&_&""")"}}),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Replaced Value",{"Index"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Custom"}, {"ClubConnections"}),
        #"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
    in
        #"Removed Columns2"
    

    I've modified the email-string to keep the "mailto:", actually, this made the code much more compact, as we can apply the same logic on both now.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 6:04 AM
    Moderator
  • Now you have to check the column and remove all leading apostrophes (" ' "), by nothing, using Find and Replace (Strg+H):


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 1:23 PM
    Moderator
  • Yes, you will have to replace after every refresh.

    Try this code:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterURLAndEmail = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER") and (Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")) ),
        ExtractString = Table.AddColumn(FilterURLAndEmail, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        IndexedWebsites = Table.AddIndexColumn(ExtractString, "Index", 0,1),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        ChgTypes = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        AddIndex = Table.AddIndexColumn(ChgTypes, "Index", 0, 1),
        MergeWithIndexedWebsits = Table.NestedJoin(AddIndex,{"Index"},IndexedWebsites,{"Index"},"NewColumn",JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(MergeWithIndexedWebsits, "NewColumn", {"Custom"}, {"ClubConnections"}),
        CreateHyperlink = Table.ReplaceValue(Expand, each [Host Club] , each "'=HYPERLINK("""&[ClubConnections]&""", """&[Host Club]&""")" ,Replacer.ReplaceText,{"Host Club"}),
        Sort = Table.Sort(CreateHyperlink,{{"Index", Order.Ascending}}),
        Cleanup = Table.RemoveColumns(Sort,{"Index", "ClubConnections"})
    in
        Cleanup

    Unfortunately you're loosing 2 links due to bad programming of the website.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:20 PM
    Moderator
  • Actually, you can maintain these links like so:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterURLAndEmail = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER") and (Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")) ),
        ExtractString = Table.AddColumn(FilterURLAndEmail, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        IndexedWebsites = Table.AddIndexColumn(ExtractString, "Index", 0,1),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        ChgTypes = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        AddIndex = Table.AddIndexColumn(ChgTypes, "Index", 0, 1),
        MergeWithIndexedWebsits = Table.NestedJoin(AddIndex,{"Index"},IndexedWebsites,{"Index"},"NewColumn",JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(MergeWithIndexedWebsits, "NewColumn", {"Custom"}, {"ClubConnections"}),
        CreateHyperlink = Table.ReplaceValue(Expand, each [ClubConnections] , each "'=HYPERLINK("""&[ClubConnections]&""", """& (if Text.Length([Host Club])>1 then [Host Club] else "MissingFriendlyName") &""")" ,Replacer.ReplaceText,{"ClubConnections"}),
        Sort = Table.Sort(CreateHyperlink,{{"Index", Order.Ascending}}),
        Cleanup = Table.RemoveColumns(Sort,{"Index", "Host Club"}),
        #"Reordered Columns" = Table.ReorderColumns(Cleanup,{"Trial Date", "ClubConnections", "Trial Location", "Trial Details", "Contact Person", "Contact Telephone (Fax)"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"ClubConnections", "Host Club"}})
    in
        #"Renamed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:31 PM
    Moderator

All replies

  • Please try this:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.Contains([Column1], "http")),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Column1], "BORDER")),
        #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """"))
    in
        #"Added Custom"

    It's a simplified version of this approach: https://blog.crossjoin.co.uk/2017/04/25/using-text-betweendelimiters-to-extract-urls-from-a-web-page-in-power-bipower-query-m/ which fits in your case and is a bit easier to follow along.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 30, 2017 7:14 AM
    Moderator
  • Hello Imke,

    This certainly did grab the links. Thankyou. I just unmarked as the answer so that I might get you back to help me further. SORRY.

    A couple of things though I guess I didn't explain,

    1. The first row of column 2 has an email address the second row of column 2 is the url , I need both.
    2. I still would like all the rest of the data from the other columns as well.
    3. It added a column 1 with all the html code from the site, can this be eliminated? I just deleted it, will that work.

    Thank you for the reply.

    Robert


    Monday, July 31, 2017 5:45 PM
  • Hi Robert,

    please try this code:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterBorder = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER")),
        #"Filtered Rows" = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "http")),
        Websites = Table.AddColumn(#"Filtered Rows", "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        #"Removed Columns" = Table.RemoveColumns(Websites,{"Column1"}),
        IndexedWebsites = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 2),
        Custom1 = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "mailto")),
        Custom2 = Table.AddColumn(Custom1, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""mailto:", """")),
        #"Trimmed Text" = Table.TransformColumns(Custom2,{{"Custom", Text.Trim}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Trimmed Text",{"Column1"}),
        IndexedEmails = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 2),
        SitesAndEmails = Table.Combine({IndexedEmails, IndexedWebsites}),
    
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},SitesAndEmails,{"Index"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Custom"}, {"ClubConnections"}),
        #"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
    in
        #"Removed Columns2"

    The last column "ClubConnections" will now contain the email & webpages.

    As you might have noticed, the website doesn't return the desired data nicely, so a bit of wrangling is required here unfortunately.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Wednesday, August 2, 2017 6:40 PM
    Moderator
  • Hello, Imke,

    Yes you did a lot of wrangling, I would have never been able to do this, I thank you very much.

    Interesting how the email and url links are there but still are not actual working links.

    If I move this new column to C rather than G, will that break it.?

    Again, I appreciate your work very much. You are very good at your job.

    Best regards,

    Robert

    Wednesday, August 2, 2017 11:44 PM
  • Sorry, lost the clicking out of sight.

    This is a bit tricky. In Excel, you can apply the "HYPERLINK"-function to do this. So you either add a column in Excel to your table to do just this, or you modify the query to return the full Excel-function. All that's left to do then in Excel is to check the column and replace ' by nothing. This will "activate" the Excel-formula and your links will become clickable:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterBorder = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER")),
        #"Filtered Rows" = Table.SelectRows(FilterBorder, each Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")),
        Websites = Table.AddColumn(#"Filtered Rows", "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        #"Removed Columns" = Table.RemoveColumns(Websites,{"Column1"}),
        IndexedWebsites = Table.AddIndexColumn(#"Removed Columns", "Index", 0,1),
        #"Replaced Value" = Table.TransformColumns(IndexedWebsites, {{"Custom", each "'=HYPERLINK("""&_&""")"}}),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Replaced Value",{"Index"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Custom"}, {"ClubConnections"}),
        #"Sorted Rows" = Table.Sort(#"Expanded NewColumn",{{"Index", Order.Ascending}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
    in
        #"Removed Columns2"
    

    I've modified the email-string to keep the "mailto:", actually, this made the code much more compact, as we can apply the same logic on both now.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 6:04 AM
    Moderator
  • Hi,

    I had tried using the =HYPERLINK option but it makes all of them weblinks so the email didn't work, I tried an alternate method with a macro and they all became email links. I was thinking of a macro that looked for http:// and made them weblinks and if not made them email links. I don't know how to do that.

    So I used your new code and now the column now looks like this; (actual names x'ed out)

    '=HYPERLINK("mailto:xxxxx.org@hotmail.com")
    '=HYPERLINK("http://www.xxxxx.org")
    '=HYPERLINK("mailto: xxxxxx@gmail.com")
    '=HYPERLINK("http://www.xxxxxxxxxxx.com")
    '=HYPERLINK("mailto:xxxxx.org@hotmail.com")

    And they are not active. 

    Thank you, Robert


    Thursday, August 3, 2017 12:52 PM
  • Now you have to check the column and remove all leading apostrophes (" ' "), by nothing, using Find and Replace (Strg+H):


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 1:23 PM
    Moderator
  • OK, That worked perfectly, Thank You Imke.

    When new data arrives will I have to remove the leading apostrophes again?

    How difficult is it to turn the text in column 'B' into these links, so they then have friendly names?I tried =Hyperlink(G2,B2) but I get a circular reference error and a '0' value is displayed that is a working email link, if I copy to the next cell still a '0' with working weblink.  I created a new column and used =Hyperlink(G2,B2) and that works perfectly, but I really don't want a new column if I can avoid it. What are my alternatives?

    Robert

     
    Thursday, August 3, 2017 2:41 PM
  • Yes, you will have to replace after every refresh.

    Try this code:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterURLAndEmail = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER") and (Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")) ),
        ExtractString = Table.AddColumn(FilterURLAndEmail, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        IndexedWebsites = Table.AddIndexColumn(ExtractString, "Index", 0,1),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        ChgTypes = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        AddIndex = Table.AddIndexColumn(ChgTypes, "Index", 0, 1),
        MergeWithIndexedWebsits = Table.NestedJoin(AddIndex,{"Index"},IndexedWebsites,{"Index"},"NewColumn",JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(MergeWithIndexedWebsits, "NewColumn", {"Custom"}, {"ClubConnections"}),
        CreateHyperlink = Table.ReplaceValue(Expand, each [Host Club] , each "'=HYPERLINK("""&[ClubConnections]&""", """&[Host Club]&""")" ,Replacer.ReplaceText,{"Host Club"}),
        Sort = Table.Sort(CreateHyperlink,{{"Index", Order.Ascending}}),
        Cleanup = Table.RemoveColumns(Sort,{"Index", "ClubConnections"})
    in
        Cleanup

    Unfortunately you're loosing 2 links due to bad programming of the website.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:20 PM
    Moderator
  • Actually, you can maintain these links like so:

    let
        Source = Lines.FromBinary(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")),
        Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        FilterURLAndEmail = Table.SelectRows(Table, each Text.Contains([Column1], "BORDER") and (Text.Contains([Column1], "http") or Text.Contains([Column1], "mailto")) ),
        ExtractString = Table.AddColumn(FilterURLAndEmail, "Custom", each Text.BetweenDelimiters([Column1], "HREF=""", """")),
        IndexedWebsites = Table.AddIndexColumn(ExtractString, "Index", 0,1),
        MainTable = Web.Page(Web.Contents("http://www.aactrialresults.com/approved/approvedtrialsAB.html")){0}[Data],
        ChgTypes = Table.TransformColumnTypes(MainTable ,{{"Trial Date", type date}, {"Host Club", type text}, {"Trial Location", type text}, {"Trial Details", type text}, {"Contact Person", type text}, {"Contact Telephone (Fax)", type text}}),
        AddIndex = Table.AddIndexColumn(ChgTypes, "Index", 0, 1),
        MergeWithIndexedWebsits = Table.NestedJoin(AddIndex,{"Index"},IndexedWebsites,{"Index"},"NewColumn",JoinKind.LeftOuter),
        Expand = Table.ExpandTableColumn(MergeWithIndexedWebsits, "NewColumn", {"Custom"}, {"ClubConnections"}),
        CreateHyperlink = Table.ReplaceValue(Expand, each [ClubConnections] , each "'=HYPERLINK("""&[ClubConnections]&""", """& (if Text.Length([Host Club])>1 then [Host Club] else "MissingFriendlyName") &""")" ,Replacer.ReplaceText,{"ClubConnections"}),
        Sort = Table.Sort(CreateHyperlink,{{"Index", Order.Ascending}}),
        Cleanup = Table.RemoveColumns(Sort,{"Index", "Host Club"}),
        #"Reordered Columns" = Table.ReorderColumns(Cleanup,{"Trial Date", "ClubConnections", "Trial Location", "Trial Details", "Contact Person", "Contact Telephone (Fax)"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"ClubConnections", "Host Club"}})
    in
        #"Renamed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:31 PM
    Moderator
  • I'm very impressed. You did it all ... Thank you so very much.

    I have a new post or thread I would like put up. I would like if you could help me with it if possible. It is far less complicated and is for a formula or macro. 

    Best regards, Robert


    Thursday, August 3, 2017 3:45 PM
  • Sure, just post link.

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 3:53 PM
    Moderator
  • Thank you:

    https://social.technet.microsoft.com/Forums/en-US/d13e9036-d913-4d07-9ef2-4b24382aed68/check-two-columns-in-two-sheets-for-match-if-match-copy-data-from-sheet2-to-sheet-1?forum=Office2016ITPro

    Robert

    Thursday, August 3, 2017 4:06 PM
  • Had a look, but cannot help. You should post real table for ppl to better understand whats required (before and after).

    Maybe MrExcel forum or StackOverflow are better for VBA-codes in your case.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, August 3, 2017 6:46 PM
    Moderator
  • OK.. Thanks for looking.
    Thursday, August 3, 2017 7:25 PM