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
 
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:

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/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

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 ;)

Impressive thankyou :)

Got it working just like your example
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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!
 
Last edited:
Upvote 0
Thanks,

I have a drop-down box with real dates then a formula in the table cell to convert the real date to YYYYMMDD
 
Upvote 0
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.


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.

VBA 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.
1589976775947.png
 
Upvote 0
maybe try to describe what you want (without html tags, bolded text etc.)
and post expected result
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
i forgot to say, this small (single cell) table will be your custom date (user defined)
 
Upvote 0
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.
 
Upvote 0
custom function
Rich (BB code):
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
sheettable.png

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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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