Help with syntax to avoid -'One or more table references a dynamic Data Source. Not able to save'

[RichardJ]

Hi,

I'm experiencing a "One or more table references a dynamic Data Source. Not able to save Dataflow" error when attempting to save a Dataflow.

The PowerBI Desktop file refreshes without issue and from the research i've done there seems to potentially be some workarounds for what i'm trying to achieve via a dataflow but i'm having trouble with the syntax.

Currently working in Desktop

let
    Source = (EndURL as text) => let
        Source = Json.Document(Web.Contents("https://urlstartpartishidden/" & EndURL)),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Expanded dataPoints" = Table.ExpandListColumn(#"Converted to Table", "dataPoints"),
        #"Expanded dataPoints1" = Table.ExpandRecordColumn(#"Expanded dataPoints", "dataPoints", {"x", "y"}, {"dataPoints.x", "dataPoints.y"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded dataPoints1",{{"dataPoints.x", type number}, {"dataPoints.y", type number}, {"dimensionX", type text}, {"dimensionY", type text}, {"isOK", type logical}, {"locationID", Int64.Type}, {"measurementID", Int64.Type}, {"operationID", Int64.Type}, {"timestamp", type datetime}, {"type", type text}, {"unitID", type text}})
    in
        #"Changed Type"
in
    Source

this post suggests that it may be possible to replace the '& EndURL' above with this type of syntax where it uses 'RelativePath' to enable the dataflow to function

Source = Web.Contents("https://www.mnb.hu/en/arfolyam-tablazat/",[RelativePath="?deviza=rbCurrencyAll&devizaSelected=ZAR&datefrom=" & DatesFrom & "&datetill=" & DatesTo & "&order=1"]),

which i've tried 

      Source = Json.Document(Web.Contents("https://urlstartpartishidden/", [RelativePath = "" & EndURL]))

but the dataflow still won't save; I think the problem is that the query doesn't know where to the find the 'EndURL' value from in order to iterate through the values.

I have the the EndURL values i'd like to iterate through in the following location

Table : Curve_Metadata
Column : URL_End

Can someone assist with helping me understand how to define the data source for the 'EndURL' so that the dataflow can successfully save/refresh.

I think I might need a command like 

EndURL = List.Transform(Curve_Metadata, each URL_End(_))

but am guessing and definitely don't know yet how to structure the query.

Thanks for any assistance
Richard

[RichardJ · Accepted solution]

Please ignore the above - this is the working version.

Will leave it here in case its of any use to anyone.

Cheers

Thanks @PhilipTreacy for the original reply on this post

let
    Source = (EndURL as text) => let
        Source = Json.Document(Web.Contents("https:/urlstartpartishidden/", [RelativePath = "" & EndURL])),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Expanded dataPoints" = Table.ExpandListColumn(#"Converted to Table", "dataPoints"),
        #"Expanded dataPoints1" = Table.ExpandRecordColumn(#"Expanded dataPoints", "dataPoints", {"x", "y"}, {"dataPoints.x", "dataPoints.y"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded dataPoints1",{{"dataPoints.x", type number}, {"dataPoints.y", type number}, {"dimensionX", type text}, {"dimensionY", type text}, {"isOK", type logical}, {"locationID", Int64.Type}, {"measurementID", Int64.Type}, {"operationID", Int64.Type}, {"timestamp", type datetime}, {"type", type text}, {"unitID", type text}})
    in
        #"Changed Type"
in
    Source