Web Table Power Query: With Dynamic URL ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a web query that runs through Powerquery with a table

And the URL ends in a date like: &Date=20190101

How can I control this date from a cell value?
I know I need to use the advanced editor but unsure how, it's nothing like VBA.

Appreciate any help
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
This is what shows in advanced editor

Code:
let
    Source = Web.Page(Web.Contents("http://example.net/test1.php?getdate=" & "20190101")),
    Data3 = Source{3}[Data],
 
Upvote 0
if in parameter source (Date) is a text date you can do nothing
if there is Excel Date (means number) you can try Number.ToText(parameter name)
because whole url is a text
 
Upvote 0
or you can try Date.ToText with/without defined format, eg. Date.ToText(Date, "yyyy/MM/dd") where Date (red) is a parameter
 
Last edited:
Upvote 0
@sandy666
Thankyou though I tried to port your example to nine and failed.

This is my advanced editor code from table from web
Rich (BB code):
let    Source = Web.Page(Web.Contents("http://intranet.trsxxcr.net/data.php?do=1000&getdate=20190115")),
    Data3 = Source{3}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID1"}, {"Column2", "Name"}, {"Column3", "Code1"}, {"Column4", "Code2"}, {"Column5", "Active"}, {"Column6", "ID2"}, {"Column7", "Date"}, {"Column8", "Year"}})
in
    #"Renamed Columns"

When I tried to merge with yours I got an error like source not defined
 
Upvote 0
I assume you want to change Source = Web.Page(Web.Contents("http://intranet.trsxxcr.net/data.php?do=1000&getdate=20190115")) via cell from the sheet
so try
Code:
[SIZE=1]
let [I]any_name[/I]=([B][COLOR="#0000FF"]Date[/COLOR][/B])=>
let    
    Source = Web.Page(Web.Contents("http://intranet.trsxxcr.net/data.php?do=1000&getdate="&Number.ToText([B][COLOR="#0000FF"]Date[/COLOR][/B]))),
    Data3 = Source{3}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID1"}, {"Column2", "Name"}, {"Column3", "Code1"}, {"Column4", "Code2"}, {"Column5", "Active"}, {"Column6", "ID2"}, {"Column7", "Date"}, {"Column8", "Year"}})
in
    #"Renamed Columns"
in [I]any_name[/I]
[/SIZE]

but first create table on the sheet, something like this:

Date
20190115
check in NameManager the name of this table (eg. Table1) and load it to PQ Editor
tab Add Column then Invoke Custom Function....
and then

expand.jpg



I can't test it because this is intranet url

btw. change the name of query (function) to eg. fnSomething, it's up to you ;)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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