# Web Table Power Query: With Dynamic URL ?



## JumboCactuar (Jan 15, 2019)

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


----------



## JumboCactuar (Jan 15, 2019)

This is what shows in advanced editor


```
let
    Source = Web.Page(Web.Contents("http://example.net/test1.php?getdate=" & "20190101")),
    Data3 = Source{3}[Data],
```


----------



## JumboCactuar (Jan 15, 2019)

Bump


----------



## sandy666 (Jan 15, 2019)

here is a working example with single parameter
adapt to your needs

PowerQuery with parameter


----------



## JumboCactuar (Jan 15, 2019)

sandy666 said:


> here is a working example with single parameter
> adapt to your needs
> 
> PowerQuery with parameter



Thankyou, this looks exactly what I need
Will try port it over

Because I'm working with dates, do I need to use textToNumber?


----------



## sandy666 (Jan 15, 2019)

On the website are numbers in URL? I don't think so


----------



## sandy666 (Jan 15, 2019)

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 (Jan 15, 2019)

or you can try Date.ToText with/without defined format, eg. Date.ToText(Date, "yyyy/MM/dd") where Date (red) is a parameter


----------



## JumboCactuar (Jan 16, 2019)

@sandy666
Thankyou though I tried to port your example to nine and failed.

This is my advanced editor code from table from web

```
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 (Jan 16, 2019)

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

```
[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


----------



## JumboCactuar (Jan 15, 2019)

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


----------



## JumboCactuar (Jan 16, 2019)

sandy666 said:


> 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
> 
> ```
> ...



Impressive thankyou 

Got it working just like your example


----------



## sandy666 (Jan 16, 2019)

You are welcome

I'm glad it works for you

btw. as you can see in the url, "date" is in yyyymmdd format (this is treated as number not as date) so if you want type real date, eg. 15/01/2019  in cell you'll need transform this table to the number format with yyyymmdd order

btw. don't quote whole post, *please!*


----------



## JumboCactuar (Jan 16, 2019)

Thanks,

I have a drop-down box with real dates then a formula in the table cell to convert the real date to YYYYMMDD


----------



## alibini (May 20, 2020)

Many thanks for any help in advance

@sandy666 i read your post above so many times but just can't quite seem to work it out.

I have almost identical issue to @JumboCactuar but maybe just don't have the expertise.

the URL i'm trying to update is here and the date is clear at the end. It is JSON.



			https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime=2020-05-20T06:00:00Z
		


I am using this in the Advanced Editor, but i'm just missing the step about how to link a cell in excel to this code.


```
let
    Source = Json.Document(Web.Contents("https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime=" & "2020-05-20" & "T06:00:00Z")),
    fieldDataCollection = Source[fieldDataCollection],
    #"Converted to Table" = Table.FromList(fieldDataCollection, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rate"}, {"Column1.id", "Column1.rate"})
in
    #"Expanded Column1"
```

*I couldn't follow your step:*

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



*[COLOR=[URL=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL"]MrExcel Message Board Migration[/URL]] ]Date[/COLOR]*20190115
<tbody>
</tbody>

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 


This is what my sheet currently looks like.


----------



## sandy666 (May 20, 2020)

maybe try to  describe what you want (without html tags, bolded text etc.)
and post expected result


----------



## alibini (May 20, 2020)

sandy666 said:


> maybe try to  describe what you want (without html tags, bolded text etc.)
> and post expected result


Thanks. All I want is that currency table to update based on another cell that contains the date. Ie the date in the url. 
thanks.


----------



## sandy666 (May 20, 2020)

post #8
change format as in original url
url cannot have spaces
then change it to function as in post #10 (ignore all BB tags)
--
create small table (one cell)
--
after that click on this small table then invoke custom function


----------



## sandy666 (May 20, 2020)

i forgot to say, this small (single cell) table will be your custom date (user defined)


----------



## alibini (May 21, 2020)

thanks. i can't work out what my custom function should be. sorry i am new to this.
my date in excel sheet can be either date format or text format
appreciate you trying to help me.


----------



## sandy666 (May 21, 2020)

*custom function*

```
let dt = (Date) =>
let
    Source = Json.Document(Web.Contents("https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime="&Date.ToText(Date, "yyyy-MM-dd")&"T06:00:00Z")),
    fieldDataCollection = Source[fieldDataCollection],
    #"Converted to Table" = Table.FromList(fieldDataCollection, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rate"}, {"Column1.id", "Column1.rate"})
in
    #"Expanded Column1"
in dt
```
*user defined date*
create table in the sheet



then load to Power Query
in PQ editor select Invoke Custom Function, choose just this small table and so on...

i cannot test this because your url doesn't work without logging in, i suppose


----------



## JumboCactuar (Jan 15, 2019)

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


----------



## alibini (May 23, 2020)

@sandy666 thanks so much for your help here. I really appreciate it. I got it working.
In the end I just used:

dateTime="&Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")&" 

Out of interest, I noticed that the date format only accepts "MM" and not "mm". Any reason for this?


----------



## sandy666 (May 23, 2020)

you are welcome
are you happy? hit Like
because of definition: Date.ToText - PowerQuery M and PQ is case sensitive


----------



## alibini (May 25, 2020)

so sorry... still having issues. these are my two queries:


```
let
    Source = Query1(null)
in
    Source
```
and

```
let dt = (Date) =>
let
    Source = Json.Document(Web.Contents("https://www.bloomberg.com/markets/api/comparison/data-bfix?currencies=AUDUSD,USDILS,EURUSD,USDJPY,GBPUSD,USDMXN,NZDUSD,USDPLN,USDCAD,USDPLN,USDCHF,USDSEK,USDCNH,USDSGD,USDCZK,USDTHB,USDDKK,USDTRY,USDHKD,USDZAR,USDHUF,XAUUSD,EURJPY,EURBGN,EURCZK,EURDKK,EURGBP,EURHUF,EURPLN,EURRON,EURSEK,EURCHF,EURNOK,EURHRK,EURRUB,EURTRY,EURAUD,EURBRL,EURCAD,EURCNY,EURHKD,EURILS,EURMXN,EURNZD,EURSGD,EURTHB,EURZAR,EURIDR,EURINR,EURKRW,EURMYR,EURPHP,AUDCAD,BRLCAD,CNYCAD,HKDCAD,INRCAD,IDRCAD,JPYCAD,MYRCAD,MXNCAD,NZDCAD,NOKCAD,PENCAD,RUBCAD,SARCAD,SGDCAD,ZARCAD,KRWCAD,SEKCAD,CHFCAD,TWDCAD,THBCAD,TRYCAD,GBPCAD,GBPJPY,CADJPY,CHFJPY,AUDJPY&dateTime="&Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")&"T06:00:00Z")),
    fieldDataCollection = Source[fieldDataCollection],
    #"Converted to Table" = Table.FromList(fieldDataCollection, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "rate"}, {"Column1.id", "Column1.rate"})
in
    #"Expanded Column1"
in dt
```
when I refresh it initially works. When I run it a day or so later, ie when the data output I am expecting to update has changed, I start getting errors.
*"[DataFormat.Error] We found extra characters at the end of JSON input."*
This appears to occur in Office 365 (32bit) but not Excel 2016 (64bit). *I need it to run on Office 365.*
Did I read somewhere that by breaking up the url you can force a refresh that overcomes this issue?

My goal is simply to have a query that updates the data based on today's date, reliably every time i hit refresh on the queries, when using Office 365.

Thanks


----------



## sandy666 (May 25, 2020)

but you hijacked someone's thread
create your own

btw. as I said I can't test you url
maybe you need be logged there, who knows?


----------

