Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to 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: 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.
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.
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.
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: 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.
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,
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.
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.
code looks good. Is
https://organisation.software.com
a valid URL that the Power BI Service can navigate to and get a 200 back?