Error - 'Can't save dataflow. One or more tables references a dynamic data source'

[julesdude7]

Hello all,

I have an issue transferring some M from desktop (where it works fine) to run in the service.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlCK1QEzDGEMIxjDGMYwUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document(Web.Contents("https://date.nager.at/api/v3/publicholidays/" & [Year] & "/GB"))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"date", "localName", "name", "countryCode", "fixed", "global", "counties", "launchYear", "types"}, {"date", "localName", "name", "countryCode", "fixed", "global", "counties", "launchYear", "types"}),
    #"Expanded counties" = Table.ExpandListColumn(#"Expanded Custom1", "counties"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded counties",{"fixed", "localName", "types", "launchYear"})
in
    #"Removed Columns"

I create a table with just the years 2020 through to 2024 and use this base column year number as part of the url for an API call, so it is dynamic. Unfortunately when I try to create this as a dataflow in the Service and paste the above code in, when saving, things seem to compile until the very end when I get a pop up error saying 'Can't save dataflow. One or more tables references a dynamic data source'.

What am I doing wrong and how can I fix this to allow this to run in the Service?

[v-jiewu-msft]

Hi @julesdude7 ,

If I understand correctly, the issue is that you encountered the error when trying to create the table as a dataflow. Please try the following methods and check if they can solve your problem:

1.Dataflows do not work with dynamic urls. You can view the following link to learn the issue.

Solved: Error - "One or more table references a dynamic Da... - Microsoft Fabric Community

2.Review any additional error messages provided in the Power BI service.

3.Check that the gateway is correctly configured and the credentials are up to date.

Best Regards,

Wisdom Wu

[julesdude7]

@v-jiewu-msft thank you.

You'll have to excuse me as I'm not the best at M. The link you provided suggests using relative path as an alternative. How would I implement this modifying the code above?