Web Table Power Query: With Dynamic URL ?

[JumboCactuar]

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]

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:

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]20190115[/TD]
[/TR]
</tbody>[/TABLE]

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

Impressive thankyou

Got it working just like your example

 

[sandy666]

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]

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]

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]

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

 

[alibini]

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]

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]

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

 

[alibini]

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]

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