Web Table Power Query: With Dynamic URL ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
677
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
677
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],
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,051
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,051
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:

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
677
@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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,051
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




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:

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,159
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top