Help Novice with query, get URL, Filter

OlderMan

OlderMan

New Member
Joined
Aug 28, 2016
Messages
9
[FONT=&quot]I have a query to a report I created on a form website and need to filter it into reports for different people.[/FONT]
[FONT=&quot]The table has a column for "Approved By" and a column "Edit Submission" which is a link.[/FONT]
[FONT=&quot]I need the Query to filter the table and only give results for a particular Approver, then I make a new query for another Approver on a new sheet.[/FONT]
[FONT=&quot]Example table: So a separate report for each of the approvers below. [/FONT]
[FONT=&quot]At the same time turn the last column "Edit Submission" into the hyperlink it is on the report. [/FONT]
[FONT=&quot]Also, if possible make the Form No. into the same link as the Edit Submission link for that row.[/FONT]
[FONT=&quot]Here is made up sample with fewer columns.[/FONT]
[FONT=&quot]Website report[/FONT]
[TABLE="width: 979"]
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">[TR]
[TD="align: center"]Form No.[/TD]
[TD]Approved by[/TD]
[TD="align: center"]Received Date[/TD]
[TD="align: center"]Approved Date[/TD]
[TD]Edit link[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]11/09/17[/TD]
[TD="align: center"]11/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD]Claude D[/TD]
[TD="align: center"]09/10/17[/TD]
[TD="align: center"]10/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD]Claude D[/TD]
[TD="align: center"]05/10/17[/TD]
[TD="align: center"]08/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]14/10/17[/TD]
[TD="align: center"]20/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]66[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]11/11/17[/TD]
[TD="align: center"]14/11/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]155[/TD]
[TD]John Smith[/TD]
[TD="align: center"]05/11/17[/TD]
[TD="align: center"]09/11/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Claude D[/TD]
[TD="align: center"]10/11/17[/TD]
[TD="align: center"]14/11/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]88[/TD]
[TD]John Smith[/TD]
[TD="align: center"]10/09/17[/TD]
[TD="align: center"]12/09/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]Filtered Excel Report for Cindy S[/FONT]
[TABLE="width: 979"]
<tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">[TR]
[TD="align: center"]Form No.[/TD]
[TD]Approved by[/TD]
[TD="align: center"]Received Date[/TD]
[TD="align: center"]Approved Date[/TD]
[TD]Edit link[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]11/09/17[/TD]
[TD="align: center"]11/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]14/10/17[/TD]
[TD="align: center"]20/10/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
[TR]
[TD="align: center"]66[/TD]
[TD]Cindy S[/TD]
[TD="align: center"]11/11/17[/TD]
[TD="align: center"]14/11/17[/TD]
[TD]Edit Submission[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]Here is what I have so far, not giving me the links.

Code: 
TABLE 0[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]let[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    
    [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Source[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Web[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Page[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]Web[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Contents[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"https://www.xxxform.com/table/72826000000000"[/FONT][/COLOR][COLOR=#666600][FONT=inherit])),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data0[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Source[/FONT][/COLOR][COLOR=#666600][FONT=inherit]{[/FONT][/COLOR][COLOR=#006666][FONT=inherit]0[/FONT][/COLOR][COLOR=#666600][FONT=inherit]}[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#660066][FONT=inherit]ExtractString[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]AddColumn[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]Data0[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Link"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Text[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]BetweenDelimiters[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Submission[/FONT][/COLOR][COLOR=#666600][FONT=inherit]],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"HREF="""[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]""""[/FONT][/COLOR][COLOR=#666600][FONT=inherit])),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#660066][FONT=inherit]IndexedWebsites[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]AddIndexColumn[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]ExtractString[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Index"[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#006666][FONT=inherit]0[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#006666][FONT=inherit]1[/FONT][/COLOR][COLOR=#666600][FONT=inherit]),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#660066][FONT=inherit]CreateHyperlink[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Table[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]ReplaceValue[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#660066][FONT=inherit]IndexedWebsites[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#666600][FONT=inherit][[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] link[/FONT][/COLOR][COLOR=#666600][FONT=inherit]][/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] each [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"'=HYPERLINK("""[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]LinkConnections[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]""", """[/FONT][/COLOR][COLOR=#666600][FONT=inherit]&[[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Edit[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#660066][FONT=inherit]Submission[/FONT][/COLOR][COLOR=#666600][FONT=inherit]]&[/FONT][/COLOR][COLOR=#008800][FONT=inherit]""")"[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#660066][FONT=inherit]Replacer[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#660066][FONT=inherit]ReplaceText[/FONT][/COLOR][COLOR=#666600][FONT=inherit],{[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Edit Submission"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Reordered Columns" = Table.ReorderColumns(IndexedWebsites,{"Date", "CAT No.", "Approved By", "Designing Judge", "Province", "Host Club", "Courses", "Trial Date", "Received date", "Approved Date", "Post-Approved", "Edit Link", "Link"}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Approved By", Order.Ascending}}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Approved By", "Approver"}, {"Designing Judge", "Designer"}, {"Province", "Prov"}, {"Host Club", "Club"}, {"Trial Date", "Trial"}, {"Received date", "Received"}, {"Approved Date", "Approved"}, {"Post-Approved", "Post-Date"}})[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]in[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]#"Renamed Columns"[/FONT][/COLOR][COLOR=#333333][FONT=&quot]
[/FONT]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You must log in or register to reply here.

Similar threads

J
  • Question Question
Extract the date to create a report date column from Source.Name column
2
Replies
10
Views
1K
lrobbo314
lrobbo314
J
  • Question Question
Excel Power Query & Power Pivot: Ageing Trend
Replies
1
Views
298
jajatidev
J
J
  • Solved
Power Query: Selection of columns with date headers
Replies
9
Views
634
GraH
GraH
E
  • Question Question
How to get the data based only from the date that the user input in power query?
Replies
1
Views
456
Kaper
Kaper
muhammad susanto
  • Solved
Power Query : Transpose To Multiple Column Based Criteria
Replies
3
Views
856
lrobbo314
lrobbo314

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top