Dynamic URL in Power Query

[Thirty_Noodles]

Hello all,

Is it possible to make a power query dynamic? For eample, if part of the URL is changed (for example within the cell of a spreadsheet or similar? I have the following Power Query:

let
    Source = Json.Document(Web.Contents("https://easytide.admiralty.co.uk/Home/GetPredictionData?stationId=0116")),
    tidalEventList = Source[tidalEventList],
    #"Converted to Table" = Table.FromList(tidalEventList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"eventType", "dateTime", "height", "isApproximateTime", "isApproximateHeight", "date"}, {"Column1.eventType", "Column1.dateTime", "Column1.height", "Column1.isApproximateTime", "Column1.isApproximateHeight", "Column1.date"})
in
    #"Expanded Column1"

Part of this query highlighted below in red needs to vary (e.g. from 0116 to 0111A) and ideally, this would be controlled via a cell dropdown or similar.

"https://easytide.admiralty.co.uk/Home/GetPredictionData?stationId=0116"


Is this achievable?

Thanks for any advice.

Noodles

 

[ExcelToDAX]

Very feasible. Create a Named Range in Excel like the screenshot below. You can make this the dropdown.

And here is what your Power Query would look like:

let
    // Get the value from the named range "SheetName"
    SheetName = Excel.CurrentWorkbook(){[Name="SheetName"]}[Content]{0}[Column1],
    
    // Create the dynamic URL
    BaseUrl = "https://easytide.admiralty.co.uk/Home/GetPredictionData?stationId=",
    DynamicUrl = BaseUrl & SheetName,
    
    // Get data from the dynamic URL
    Source = Json.Document(Web.Contents(DynamicUrl)),
    tidalEventList = Source[tidalEventList],
    #"Converted to Table" = Table.FromList(tidalEventList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"eventType", "dateTime", "height", "isApproximateTime", "isApproximateHeight", "date"}, {"Column1.eventType", "Column1.dateTime", "Column1.height", "Column1.isApproximateTime", "Column1.isApproximateHeight", "Column1.date"})
in
    #"Expanded Column1"

 

[Thirty_Noodles]

ExcelToDAX, this is genius!

Thanks very much for the swift response; it worked a treat!

Noodles.

 

[Peter_SSs]

ExcelToDAX, this is genius!

Thanks very much for the swift response; it worked a treat!

Noodles.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.