PowerBI data refresh due to dynamic data source. Help with adjusting the M script

[mphillenga]

Hi,

I have developed a report that fetches data from an API. I have build the URL in such a way that it should grab the newest data, everytime the data is refreshed.

When refreshing the data in PowerBI web app the folling error pops up: "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources."

I've read a couple of articles and videos about this issue and understand that it can be solved by taking out the dynamic part and replacing it with a RelativePath.

However I'm struggling how to exactly build this for the URL that I'm using.

I have made some changes, but I continue to get the same error message:

let
    // Set the fixed start date
    FixedStartDate = #date(2024, 1, 1),

    // Today's date dynamically calculated
    CurrentDate = Date.From(DateTime.LocalNow()),

    // Format dates
    PeriodFromDate = Date.ToText(FixedStartDate, "dd-MM-yyyy"),
    PeriodToDate = Date.ToText(CurrentDate, "dd-MM-yyyy"),

    // Define query parameters directly as a record
    QueryParams = [
        onlyReportColumns = "no",
        periodFromDate = PeriodFromDate,
        periodToDate = PeriodToDate
    ],

    // Directly specify the full base URL and relative path in Web.Contents (no variables!)
    Source = Json.Document(
        Web.Contents(
            "https://organisation.software.com", 
            [
                RelativePath = "api/v1/reportDirectGet/projectHoursEmpWeek",
                Query = QueryParams
            ]
        )
    ),

    // Process the data as before
    Data = Source[jsondata],
    ConvertToTable = Table.FromList(Data, Record.FieldValues, Record.FieldNames(Data{0})),
    AutoTypeConversion = Table.TransformColumnTypes(ConvertToTable, 
        List.Transform(Table.ColumnNames(ConvertToTable), each {_, type text}), 
        "en-US"
    )
in
    AutoTypeConversion

Does anyone have any idea of why it's still not working, and what I need to change?

Thanks a lot.

[v-veshwara-msft]

Hi @mphillenga ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @mphillenga ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @mphillenga ,

Thanks for reaching out in Microsoft Fabric Community.
The error "This dataset includes a dynamic data source..." occurs because the query still generates parts of the Web API request dynamically at runtime, particularly by using DateTime.LocalNow(). Power BI Service requires the URL to be fully evaluable at design time for scheduled refresh to work. Even though the dates are passed as parameters, the use of DateTime.LocalNow() results in dynamic URL generation at runtime, which causes the issue.

To address this, you can restructure the query to use static parameters, like this:

Create two parameters:

StartDate (Type: Date, e.g., 2025-01-01)

EndDate (Type: Date, e.g., 2025-04-28)

Adjust the query like this:

let
    FixedStartDate = StartDate,
    FixedEndDate = EndDate,

    PeriodFromDate = Date.ToText(FixedStartDate, "dd-MM-yyyy"),
    PeriodToDate = Date.ToText(FixedEndDate, "dd-MM-yyyy"),

    QueryParams = [
        onlyReportColumns = "no",
        periodFromDate = PeriodFromDate,
        periodToDate = PeriodToDate
    ],

    Source = Json.Document(
        Web.Contents(
            "https://organisation.software.com", 
            [
                RelativePath = "api/v1/reportDirectGet/projectHoursEmpWeek",
                Query = QueryParams
            ]
        )
    ),

    Data = Source[jsondata],
    ConvertToTable = Table.FromList(Data, Record.FieldValues, Record.FieldNames(Data{0})),
    AutoTypeConversion = Table.TransformColumnTypes(ConvertToTable, 
        List.Transform(Table.ColumnNames(ConvertToTable), each {_, type text}), 
        "en-US"
    )
in
    AutoTypeConversion

Some Helpful references:
Data refresh in Power BI - Power BI | Microsoft Learn

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[mphillenga]

Hi @v-veshwara-msft,

Thanks a lot.

To clarify, I'd like to get the latest data from the API everytime the PowerBI refreshes. Therefore I did not put a fixed EndDate in, but the "CurrentDate = Date.From(DateTime.LocalNow())" code.

Do you have idea how to ensure the PowerBI service request refreshes automatically, whilst grabbing the newest/ latest data every day?

Thanks,

[v-veshwara-msft]

Thanks for clarifying.

In Power BI Service, even though you want dynamic latest data, the query (including dates) must be static at refresh time. Using DateTime.LocalNow() makes the URL dynamic, which the Service cannot evaluate during scheduled refresh.

To solve this:

If your API supports it, skip passing periodToDate, so the API automatically returns up-to-today data.

Otherwise, use a parameter for EndDate and update it daily, either manually or using Power Automate.

Power BI needs the URL and its structure to be static, but the data can still change each day based on how your API handles missing or fixed parameters.

Hope this helps and let us know if you need any further assistance.

Please consider marking the helpful reply as Accepted Solution to assist others with similar issues.

Thanks,

Vinay.

[mphillenga]

Hi @lbendlin,

Thanks for your quick response.

The base URL works absolutely fine. I've changed it for privacy purposes, but in the actual solution it is working. In earlier version of the M script, where I encountered the refresh problem first, the URL was working fine.

It still seems to have an issue with the dynamic parts of the M script. But I can't understand why or how to change.

[lbendlin]

code looks good.  Is 

https://organisation.software.com

a valid URL that the Power BI Service can navigate to and get a 200 back?