Fabric Dataflow Gen2 - Dynamic data sources are not supported

Pete_Hr — Thu, 12 Dec 2024 22:06:03 GMT

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

Re: Fabric Dataflow Gen2 - Dynamic data sources are not supported

lbendlin — Thu, 12 Dec 2024 23:34:08 GMT

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

Re: Fabric Dataflow Gen2 - Dynamic data sources are not supported

Pete_Hr — Wed, 25 Dec 2024 14:00:25 GMT

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.

topic Fabric Dataflow Gen2 - Dynamic data sources are not supported in Dataflow

Fabric Dataflow Gen2 - Dynamic data sources are not supported

Re: Fabric Dataflow Gen2 - Dynamic data sources are not supported

Re: Fabric Dataflow Gen2 - Dynamic data sources are not supported