Power Query from dynamic URL

Hey Guys,

so I am not too sure about my skillset but I would say im advanced ( I have played around a fair bit with power querys but mostly simple stuff as reformatting data, I havent had anything to do yet with VBA). I am using Excel 2021

I want to do the following:

Every Month, 2 big companies publish a "newsletter" with their new prices. This newsletter can be accessed by a url and is structured like this (as an example):

https://www.google.com/sites/default/files/documents/YEARMONTH%WEIRDTEXTHERE

so currently its:

https://www.google.com/sites/default/files/documents/202004%WEIRDTEXTHERE

Now I want to create a query with this URL which should automatically show the current prices. This is a bit tricky as the Prices for the next month are already being released on the 21. of each month (or the next workday).

I am guessing that I need 2 Parameters (/year and /month) but then also a way of automatically setting this parameter, which I have not yet found a way on how to do.

Can you guys please point me in the right direction on how to tackle this?

THank you

That could be like

let
    thisDate = Date.From(DateTime.LocalNow()),
    day = Date.Day(thisDate),
    adjustedDate =
        if day > 21
        then Date.AddDays( thisDate, 15)
        else thisDate,
    year = Text.From( Date.Year(adjustedDate) ),
    month = Number.ToText( Date.Month(adjustedDate), "00"),
    URL = "https://www.google.com/sites/default/files/documents/" & 
           year & month & "%WEIRDTEXTHERE"
in
    URL

let thisDate = Date.From(DateTime.LocalNow()), day = Date.Day(thisDate), adjustedDate = if day > 21 then Date.AddDays( thisDate, 15) else thisDate, year = Text.From( Date.Year(adjustedDate) ), month = Number.ToText( Date.Month(adjustedDate), "00"), URL = "https://www.google.com/sites/default/files/documents/" & year & month & "%WEIRDTEXTHERE" in URL

Forum Discussion

Power Query from dynamic URL

Share

Resources