Fabric Dataflow Gen2 - Dynamic data sources are not supported

[Pete_Hr]

Hello, I am what would probably be considered an intermediate citizen developer.  I'm getting a "dynamic data sources are not supported" error when publishing a Dataflow Gen 2 to the Fabric service.  After using Copilot, ChatGPT, and Google/Bing searches, I see other have a similar issue, but have been unable to glean a solid solution.  I'm basically trying to get the Latitude and Longiture based on address information stored in a SQL database.  I use a service (much like the Google Maps API) to provide the address and drop the Lat and Long into newly created column in the Dataflow.  It all works fine until I publish and get the error.  I know the reason why based on all my research, but I've been unable to find a solution which I can contain to the Dataflow.  Below is my M code.  Any help on how to do a work around would be gratly appreciated!

let
// Original query to get the Property table
Source = Sql.Database("ServerName", "CorpRealEstate"),
Navigation1 = Source{[Schema = "dbo", Item = "Property"]}[Data],

// Add FullAddress column
AddedFullAddress = Table.AddColumn(Navigation1, "FullAddress", each [Address] & ", " & [City] & ", " & [State] & ", " & [ZipCode] & ", " & [Country]),

// Add GetLatLongURL column
AddedGetLatLongURL = Table.TransformColumnTypes(Table.AddColumn(AddedFullAddress, "GetLatLongURL", each "https://locationservices-aws.com/home/places?address=" & [FullAddress]), {{"GetLatLongURL", type text}}),

// Add a custom column to make the web request and get the latitude and longitude
AddLatLong = Table.AddColumn(AddedGetLatLongURL, "LatLong", each let
BaseUrl = "https://locationservices-aws.com/",
RelativePath = "home/places",
Query = [address = [FullAddress]],
Source = Json.Document(Web.Contents(BaseUrl, [RelativePath = RelativePath, Query = Query])),
Latitude = Source{1},
Longitude = Source{0}
in
[Latitude = Latitude, Longitude = Longitude]),

// Expand the LatLong column into separate Latitude and Longitude columns
ExpandLatLong = Table.ExpandRecordColumn(AddLatLong, "LatLong", {"Latitude", "Longitude"})
in
ExpandLatLong

[Pete_Hr]

Hi there, thanks for the suggestion.  However, after trying multiple ways, I continue to get the same error.  It seems as though this might be an issue with Fabric Dataflow Gen 2.  This works if I do it in Power BI desktop and publish to the Power BI service.

[lbendlin · Accepted solution]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1