none
Extract URL's RRS feed

  • Question

  • I have Query to a report I created on a form website and need to filter it into reports for different people.

    The table has a column "Edit Submission" which is a link. 

    I need the last column "Edit Submission" into the hyperlink it is on the report. 

    and if possible make the Form No. column values into the same link as the Edit Submission link.

    Here is what I have so far:

    let
        
        Source = Web.Page(Web.Contents("https://www.jotform.com/table/72826043108048")),
        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Date", type text}, {"Designing Judge", type text}, {"Approved By", type text}, {"Province", type text}, {"Host Club", type text}, {"Courses", Int64.Type}, {"Trial Date", type text}, {"Received date", type text}, {"Approved Date", type text}, {"Post-Approved", type text}, {"CAT No.", type text}, {"Edit Link", type text}}),
        #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByPositions({0, 11}, false), {"Date.1", "Date.2"}),
        #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Date.2"}),
        ExtractString = Table.AddColumn(#"Changed Type", "Link", each Text.BetweenDelimiters( "HREF=""",""""[Edit Submission])),
        IndexedWebsites = Table.AddIndexColumn(ExtractString, "Index", 0, 1), 
        CreateHyperlink = Table.ReplaceValue(IndexedWebsites, each [Edit Link] , each "'=HYPERLINK("""&[LinkConnections]&""", """&[Edit Submission]&""")" ,Replacer.ReplaceText,{"Edit Submission"}),
         #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","No.","",Replacer.ReplaceText,{"CAT No."}),
        #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Approved By", "Approver"}, {"Designing Judge", "Designer"}, {"Province", "Prov"}, {"Host Club", "Club"}, {"Trial Date", "Trial"}, {"Received date", "Received"}, {"Approved Date", "Approved"}, {"Post-Approved", "Post-Date"}, {"CAT No.", "No."}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"No.", "Date.1", "Approver", "Designer", "Prov", "Club", "Courses", "Trial", "Received", "Approved", "Post-Date", "Edit Link"}),
        #"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Approver] = "Anic Vermette"))
    in
        #"Filtered Rows" 


    Thank you, Robert




    Sunday, October 15, 2017 2:45 AM

Answers

  • Hey Robert,

    I'm not aware of the HYPERLINK function working inside of Power Query, and the values that outputs Power Query are in plain text, date or numeric and not in the expression that Excel requires in order to execute the HYPERLINK function.

    Just to rephrase, you can not create a hyperlink inside of Power Query. You can only create that when the values are in an Excel table using the "HYPERLINK" function.

    My suggestion would be to create a macro that will output the query into a table and then create a new column using the HYPERLINK function.

    Wednesday, October 18, 2017 3:53 AM

All replies

  • Hey Robert,

    Let me know if this is closer to what you're after:

    let
        
        Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.jotform.com/table/72826043108048"))}),
        #"Filas filtradas" = Table.SelectRows(Source, each Text.Contains([Column1], "edit/")),
        #"Dividir columna por delimitador" = Table.SplitColumn(#"Filas filtradas", "Column1", Splitter.SplitTextByDelimiter("</td><td>", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
        #"Tipo cambiado" = Table.TransformColumnTypes(#"Dividir columna por delimitador",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", Int64.Type}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}}),
        #"Dividir columna por delimitador1" = Table.SplitColumn(#"Tipo cambiado", "Column1.12", Splitter.SplitTextByDelimiter("href='", QuoteStyle.Csv), {"Column1.12.1", "Column1.12.2"}),
        #"Valor reemplazado" = Table.ReplaceValue(#"Dividir columna por delimitador1","<tr><td>","",Replacer.ReplaceText,{"Column1.1"}),
        #"Dividir columna por delimitador2" = Table.SplitColumn(#"Valor reemplazado", "Column1.12.2", Splitter.SplitTextByDelimiter("'>", QuoteStyle.Csv), {"Column1.12.2.1", "Column1.12.2.2"}),
        #"Tipo cambiado1" = Table.TransformColumnTypes(#"Dividir columna por delimitador2",{{"Column1.12.1", type text}, {"Column1.12.2.1", type text}, {"Column1.12.2.2", type text}}),
        #"Columnas con nombre cambiado" = Table.RenameColumns(#"Tipo cambiado1",{{"Column1.12.2.1", "url"}}),
        #"Columnas quitadas" = Table.RemoveColumns(#"Columnas con nombre cambiado",{"Column1.12.1", "Column1.12.2.2"})
    in
        #"Columnas quitadas"

    best!


    Tuesday, October 17, 2017 2:49 AM
  • Hello Miguel,

    Thanks... This got me off guard with the Spanish... I had to think about the steps a bit longer. Unfortunately it is not the answer. I'm still getting an error for the column [Edit Link].

    What I need is the column [Edit Link] with the hyperlinks [Edit Submission] to become links on the sheet.  If you view my table at the website ( https://www.jotform.com/table/72826043108048 ) you may get  better idea. So I just need the links to work again.

    regards, Robert

    Tuesday, October 17, 2017 2:10 PM
  • Hey Robbert,

    I believe the code above is exactly what you're after taking in consideration your last reply.

    Power Query doesn't output hyperlinks, but rather just pure text. What the code does is that it extracts the "friendly name" and "url" fields that you'll be using alongside the HYPERLINK function inside of Excel. Basically, once you have the output table, from PQ, into Excel then you'll be creating a new column using the HYPERLINK function so that people can have that "Edit Submission" with the correct underlying link

    Tuesday, October 17, 2017 4:50 PM
  • Hi Miguel, 

    Sorry I did't copy the code properly. Yes the links are there. TNX

    I would like you to look at this bit of code and tell me how I could incorporate it. I used it in a previous PQ some time back but just can't see how to alter it to fit. 

        Expand = Table.ExpandTableColumn(MergeWithIndexedWebsits, "NewColumn", {"Custom"}, {"EditConnections"}),
        CreateHyperlink = Table.ReplaceValue(Expand, each [Edit Link] , each "'=HYPERLINK("""&[EditConnections]&""", """& (if Text.Length([Edit Submission])>1 then [Edit Link] else "MissingFriendlyName") &""")" ,Replacer.ReplaceText,{"Edit Submissions"}),
        Sort = Table.Sort(CreateHyperlink,{{"Index", Order.Ascending}}),
        Cleanup = Table.RemoveColumns(Sort,{"Index", "Host Club"}),

    I have changed some of the names to reflect this form. On the the other sheet it made the links active with a friendly name.

    If you like I can post the complete code for you.

     
    Tuesday, October 17, 2017 8:44 PM
  • Hey Robert,

    I'm not aware of the HYPERLINK function working inside of Power Query, and the values that outputs Power Query are in plain text, date or numeric and not in the expression that Excel requires in order to execute the HYPERLINK function.

    Just to rephrase, you can not create a hyperlink inside of Power Query. You can only create that when the values are in an Excel table using the "HYPERLINK" function.

    My suggestion would be to create a macro that will output the query into a table and then create a new column using the HYPERLINK function.

    Wednesday, October 18, 2017 3:53 AM
  • Hi Miguel,

    Well not being a programmer I cannot argue the point. I can only tell you that a question I asked in August 2017 was answered by a moderator who created code that accomplished this. I showed you a small part of the code she created. If you like I can give you the complete code to verify.

    Miguel, I'm using the code you created with some edits to suite my needs and it is doing exactly as you indicated it should.

    Have a good and I Thank very much.

    Best regards, Robert

    Thursday, October 19, 2017 2:14 PM
  • This was the HYPERLINK-workaround if you don't want to use VBA: http://www.thebiccountant.com/2017/09/12/powerquery-return-clickable-hyperlinks-to-excel/

    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, October 19, 2017 9:59 PM
    Moderator
  • This was the HYPERLINK-workaround if you don't want to use VBA: http://www.thebiccountant.com/2017/09/12/powerquery-return-clickable-hyperlinks-to-excel/


    Hello Imke,

    You may recall having helped me with a similar requirement in July 29. (Extracting hyperlinks from webpage table) 

    Miguel offered up some code I was able to use and I modified it to suit. 

    My edits may be a bit ugly but it gets me very close to my needs. As I recall you were actually able to create the hyperlinks with friendly names on my sheet. I have been trying to incorporate your code in this application and given enough time I might succeed. 

    I looked at the work around and when I use this code to add the Friendly Name I get the '=HYPERLINK('"url'","CAT#"). When I replace ' I get the CAT # only that is a link. 

    I must be missing a step so that the CAT# appears on the sheet that is a link like you indicate. 

    Here is my code todate;

    let
        
        Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.jotform.com/table/72826043108048"))}),
        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "edit/")),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter("</td><td>", QuoteStyle.Csv), {"CAT", "Date", "Designer", "Approver", "Prov.", "Club", "Courses", "Trial", "Received", "Approved", "Post Approve", "URL"}),
        #"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"CAT", each Text.AfterDelimiter(_, "<tr><td>", 0), type text}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Date", type text}, {"Designer", type text}, {"Approver", type text}, {"Prov.", type text}, {"Club", type text}, {"Courses", Int64.Type}, {"Trial", type text}, {"Received", type text}, {"Approved", type text}, {"Post Approve", type text}, {"CAT", type text}, {"URL", type text}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "URL", Splitter.SplitTextByDelimiter("href='", QuoteStyle.Csv), {"URL.1", "URL.2"}),
        #"Split Column by Position" = Table.SplitColumn(#"Split Column by Delimiter1", "Date", Splitter.SplitTextByRepeatedLengths(10), {"Date", "Date.2"}),
        #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Date.2", "URL.1"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns", "URL.2", Splitter.SplitTextByDelimiter(">", QuoteStyle.Csv), {"URL"}),
        #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter2", "Edit Link", each "'=HYPERLINK(""" & [URL] & ""","""&[CAT]&""")"),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"URL"})
    in
        #"Removed Columns1"

    Thank you for responding.

     







    Friday, October 20, 2017 2:20 PM
  • Hi Robert,

    not sure what the current problem is.

    Your code delivers what I would expect: In column "Edit Link" are the hyperlinks with friendly names (Number from [CAT]-column) after you've made the replacement. You can format it as hyperlink as well.

    But as they are just 3-digit-number, there is not much space for the mouse to hover over. So you might consider to add some symbols here (like underscores or dots) to expand it a bit.


    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!

    Saturday, October 21, 2017 12:23 PM
    Moderator
  • Hi Imke,

    Well I'm not getting what you show. See below. Then I have to remove the apostrophe with MACRO. 

    Good idea I will add additional text. I assume I can do that in "M".

    I was considering "Submission_"[CAT]

    I notice the link is column L and not K, probably nothing but thought I ask.

    Regards, Robert


    Saturday, October 21, 2017 4:58 PM