OlderMan
New Member
- Joined
- Aug 28, 2016
- Messages
- 9
I have a 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 want "Edit Submission" into the hyperlink it is on the report, and
if possible make the values in No. column the same link as the Edit Submission column links.
Here is what I have so far, but I'm not getting the links.
The table has a column "Edit Submission" which is a link. I want "Edit Submission" into the hyperlink it is on the report, and
if possible make the values in No. column the same link as the Edit Submission column links.
Here is what I have so far, but I'm not getting the links.
Code:
TABLE 0
Code:
let
Source = Web.Page(Web.Contents("https://www.#/table/#")),
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"
[COLOR=#880000][FONT=inherit]
[/FONT][/COLOR]
Last edited: