The dataset includes a dynamic data source message

[Richard_Halsall]

Hi

Looking for help with a Power BI Service Refresh and a web API and the dynamic data source message.

The query is constructed as follows:

let

    Asset = 

let

    url = "https://anydomain.com/tapi/v1/asset/list",

    headers = [

        #"X-Auth-Token" = "Token",

        #"accept" = "application/json",

        #"Content-Type" = "application/json"

    ],

webdata = Web.Contents(url, [Headers=headers]),

    response = Json.Document(webdata),

    Source = response

    in 

    response,

    #"Converted to Table" = Record.ToTable(Asset),

    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),

    #"Expanded Value2" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id"}, {"AssetID"}),

    #"Removed Duplicates" = Table.Distinct(#"Expanded Value2", {"AssetID"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"AssetID", type text}}),

    #"Added fAssetID" = Table.AddColumn(#"Changed Type1", "WebData", each fAssetID([AssetID])),

    #"Expanded WebData1" = Table.ExpandTableColumn(#"Added fAssetID", "WebData", {"siteID", "assetTypeID", "title", "turbine-model", "turbine-model_title", "year_title", "turbine-pad-id_title", "turbine-id", "blade-id", "blade-position_title"}, {"siteID", "assetTypeID", "title", "turbine-model", "turbine-model_title", "year_title", "turbine-pad-id_title", "turbine-id", "blade-id", "blade-position_title"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded WebData1",{{"AssetID", Int64.Type}}),

    #"Added AssetType" = Table.AddColumn(#"Changed Type", "AssetType", each if [AssetID] = 2 then "Blade" else if [AssetID] = 4 then "Wind Turbine" else if [AssetID] = 11 then "Nacelle" else if [AssetID] = 9 then "T Pylon" else "No Asset", type text)

in

    #"Added AssetType"

The function it calls at the step  #"Added fAssetID" is constructed as follows:

let LoadAssetWebDetails = (assetdetailID as text) =>

let

    AssetDetail = 

    let     

    url = "https://anydomain.com/tapi/v1/asset/get?assetID="&assetdetailID&"",

    headers = [

        #"X-Auth-Token" = "Token",

        #"accept" = "application/json",

        #"Content-Type" = "application/json"

    ],

webdata = Web.Contents(url, [Headers=headers]),

    response = Json.Document(webdata),

    Source = response

    in 

    response,

    #"Converted to Table" = Record.ToTable(AssetDetail),

    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")

in #"Pivoted Column"

in LoadAssetWebDetails

I believe the fault lies within the code for the function  "fAssetID" as when I construct a similar query calling the same api but without the function it refreshes fine in Power Bi Service

Any advice would be much appreciated. Thanks

[v-yohua-msft · Accepted solution]

Hi, @Richard_Halsall 

The Power BI service needs a stable, predictable URL pattern to allow refreshes. When using dynamic URLs, especially URLs constructed with changed parameters, such as URLs in functions, Power BI may not validate or refresh the data source correctly.

Here are a few steps you can take to troubleshoot and potentially resolve this issue:

Make sure that the initial call uses a static URL that doesn't change. This is critical for Power BI to perform metadata discovery. If necessary, you can use a virtual URL that returns the JSON structure to a dynamic call for initial loading and metadata discovery.

Use the RelativePath and Query options, and instead of concatenating the URL directly with the parameters, use the Neutral option. This method is more friendly to dynamic URLs.

Review the Power BI gateway configuration. If you're using an on-premises data gateway, make sure that the gateway is properly configured for your data source. Since you're accessing a web API, the gateway may not be directly involved, but it's a good idea to check if other parts of the data model need it.

Check for unsupported functions. Refresh some M functions aren't supported in the Power BI service. Although supported, the way it is used, especially dynamic content, can sometimes cause problems. For more details, check out the documentation on data source limitations：

Troubleshooting unsupported data source for refresh - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @Richard_Halsall 

Here is some threads that have same problem as yours

Search - Microsoft Fabric Community