Cant understand why this is a dynamic data source?

[mike_asplin]

I have a function "FX" getting some data from a web page

(Month as text) as table =>
let
  Source = Web.BrowserContents("https://www.trade-tariff.service.gov.uk/exchange_rates/view/"& Month & "?type=monthly"),
  #"Extracted table from HTML" = Html.Table(Source, {{"Column0", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(1)"}, {"Column1", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(2)"}, {"Column2", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(3)"}, {"Column3", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(4)"}, {"Column4", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(5)"}, {"Column5", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(6)"}}, [RowSelector = "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR"]),
  #"Promoted headers" = Table.PromoteHeaders(#"Extracted table from HTML", [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Currency units per £1", type number}, {"Start date", type date}, {"End date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed column type", each ([#"Country/territory"] = "Eurozone" or [#"Country/territory"] = "USA")),
    #"Choose columns" = Table.SelectColumns(#"Filtered Rows2", {"Currency code", "Currency units per £1", "Start date", "End date"}),
    #"Pivoted Column" = Table.Pivot(#"Choose columns", List.Distinct(#"Choose columns"[#"Currency code"]), "Currency code", "Currency units per £1", List.Sum)
in
    #"Pivoted Column"

I used that to get the fx rates for a bunch of months with this query

let
    Today = Date.From( DateTime.LocalNow() ),
    StartDate = #date(2022, 1, 1),
    EndDate = Date.AddYears(Date.EndOfYear( Today ),0),
    #"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
    #"Insert YYYY-MM" = Table.AddColumn(#"Converted to Table", "Month", each Date.ToText( [Date], "yyyy-MM"), type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Insert YYYY-MM",{"Month"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Month", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "FX", each try FX([Month])),
    #"Expanded FX" = Table.ExpandRecordColumn(#"Added Custom1", "FX", {"Value"}, {"Value"}),
    #"Expanded Value" = Table.ExpandTableColumn(#"Expanded FX", "Value", {"Start date", "End date", "EUR", "USD"}, {"Start date raw", "End date raw", "EUR", "USD"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Value",{{"Start date raw", type date}, {"End date raw", type date}, {"EUR", type number}, {"USD", type number}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Start date", each Date.StartOfMonth(Date.AddMonths([Start date raw], -1))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "End date", each Date.EndOfMonth(Date.AddMonths([End date raw], -1))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"Start date", type date}, {"End date", type date}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type2",{"Start date", "End date", "EUR", "USD"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each ([Start date] <> null)),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"Start date", Order.Ascending}})
in
    #"Sorted Rows1"

I had this as part of a larger model where the rest of the data was coming from Fabric.  Originally i was leaning on date coming out of Fabric so assumed that was the issue. I thinkI have now created it so it is completely stand alone. The data sources says there are no sources, but " some data sources not listed because of hand-authored queries"? 

PBIX is here FX Test 

Can someone tell me what I'm doing wrong as seems fine to me!!!

Thanks

[v-sshirivolu]

Hi @mike_asplin ,

I would take a moment to thank @MarkLaf  and @GeraldGEmerick ,for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[MarkLaf · Accepted solution]

GeraldGEmerick's response is spot on. Additionally, if you are trying to refresh in the service, last I checked Web.BrowserContents is not supported. You can get around this, though, with Web.Contents + Text.FromBinary.

To expand on Gerarld's response, you want to move the dynamic parts of your base url to RelativePath in the options param (details can be found here: https://learn.microsoft.com/en-us/powerquery-m/web-contents). To put it all together with a concrete example (that I think gets you what you want, but may need additional testing):

(Month as text) as table =>
let
    // Fixed (🤞) M
    Source = Web.Contents(
        "https://www.trade-tariff.service.gov.uk",[
            RelativePath="/exchange_rates/view/" & Month,
            Headers=[],
            Query=[type="monthly"]
        ]
    ),
    ToHtml = Text.FromBinary( Source ),

    // Original M
    #"Extracted table from HTML" = Html.Table(ToHtml, {{"Column0", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(1)"}, {"Column1", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(2)"}, {"Column2", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(3)"}, {"Column3", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(4)"}, {"Column4", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(5)"}, {"Column5", "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR > :nth-child(6)"}}, [RowSelector = "TABLE.govuk-table.govuk-\!-margin-top-6 > * > TR"]),
    #"Promoted headers" = Table.PromoteHeaders(#"Extracted table from HTML", [PromoteAllScalars = true]),
    #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"Currency units per £1", type number}, {"Start date", type date}, {"End date", type date}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed column type", each ([#"Country/territory"] = "Eurozone" or [#"Country/territory"] = "USA")),
    #"Choose columns" = Table.SelectColumns(#"Filtered Rows2", {"Currency code", "Currency units per £1", "Start date", "End date"}),
    #"Pivoted Column" = Table.Pivot(#"Choose columns", List.Distinct(#"Choose columns"[#"Currency code"]), "Currency code", "Currency units per £1", List.Sum)
in
    #"Pivoted Column"

[mike_asplin]

Excellent all working thanks so much for detailed explanation 

[GeraldGEmerick]

@mike_asplin Generally, if you are building a URL string like what you are doing in your function, that is considered a dynamic data source by the Power BI Service. That is a common issue. To make them refreshable, you must structure your Web.Contents calls so that the base URL is static and only parameters change dynamically.