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

 

[alibini]

@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]

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

 

[alibini]

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]

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?