Data Not Loading Into Power BI API Web Connector

[Griggs]

I have been using the Query below for over a year without any issues, but since last week, the report that was using it cannot run. Upon investigation, I found that I can see the data in Power Query but when trying to load it into Power BI it gets stuck on 'Evaluating' indefinitely. Is this a known error in Power BI since the last update or is there something wrong with the query (below)? As I said, this worked flawlessly for over a year, so I can only assume the update caused a conflict.

I found that, in order for Power BI to parse the URL when published, I had to give it a full, working URL first and apply the variables as a query afterward – rather than hard coding the variables into the URL.

let
todayDate = DateTime.Date(DateTime.LocalNow()),
yesterdayDate = Date.From((todayDate) - #duration(1, 0, 0, 0)),
subtractedDate = Date.From((todayDate) - #duration(7, 0, 0, 0)),
formattedDate = Date.ToText(todayDate, "yyyy-MM-dd"),
formattedlastDate = Date.ToText(subtractedDate, "yyyy-MM-dd"),
Source = Json.Document(Web.Contents("https://www.findmyshift.co.uk/api/1.4/reports/hours?apiKey=[KEY]&from=2025-01-01&to=2025-01-01", [Query=[from=formattedlastDate] & [to=formattedDate]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teamId", "shiftId", "staffId", "facilityId", "firstName", "lastName", "payrollId", "day", "date", "start", "finish", "break", "breakStart", "breakFinish", "facility", "comments", "hourlyRate", "rateType", "rateCode", "rateMultiplier", "hours", "cost", "tax"}, {"teamId", "shiftId", "staffId", "facilityId", "firstName", "lastName", "payrollId", "day", "date", "start", "finish", "break", "breakStart", "breakFinish", "facility", "comments", "hourlyRate", "rateType", "rateCode", "rateMultiplier", "hours", "cost", "tax"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"teamId", type text}, {"shiftId", type text}, {"staffId", type text}, {"facilityId", type any}, {"firstName", type text}, {"lastName", type text}, {"payrollId", type text}, {"day", type text}, {"date", type date}, {"start", type datetime}, {"finish", type datetime}, {"break", type number}, {"breakStart", type any}, {"breakFinish", type any}, {"facility", type any}, {"comments", type text}, {"hourlyRate", Int64.Type}, {"rateType", type text}, {"rateCode", type any}, {"rateMultiplier", Int64.Type}, {"hours", type number}, {"cost", Int64.Type}, {"tax", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"teamId", "shiftId", "staffId", "facilityId", "payrollId", "break", "breakStart", "breakFinish", "facility", "hourlyRate", "rateCode", "rateMultiplier", "cost", "tax"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"firstName", Text.Trim, type text}, {"lastName", Text.Trim, type text}}),
#"Merged Columns" = Table.CombineColumns(#"Trimmed Text",{"firstName", "lastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"FMS Name"),
#"Removed Duplicates" = Table.Distinct(#"Merged Columns")
in
#"Removed Duplicates"

[Anonymous]

Hi @Griggs 

Have you made any changes to your query code?

There are several discussions in the Microsoft Fabric Community forums where users have faced similar issues for your reference:

Solved: PowerBI: Refresh is stuck on "evaluating" - Microsoft Fabric Community

Solved: How to avoid pbi evaluating take very long - Microsoft Fabric Community

Given I have so little information at this time, I can't tell what's causing your problem.You can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support:  https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you for your understanding.

Best Regards

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

[danextian]

Hi @Griggs 

I found that, in order for Power BI to parse the URL when published, I had to give it a full, working URL first and apply the variables as a query afterward – rather than hard coding the variables into the URL.

I think you're talking about the dynamic data source error when trying to refresh a web query in the service. This happens when you use a parameterized url string that isnt using the relative path option of Web.Contents. The parameterized URL string below will not refresh in the service.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjXSMzVS0lEyNNYzMQTSRgZGproGhrqGxkgcI3MgpyQ1tyC1KLGktCg13ihXKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [latitude = _t, longitude = _t, startdate = _t, enddate = _t, hourly = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Json.Document( Web.Contents(
"https://archive-api.open-meteo.com/v1/archive?latitude=" & [latitude] & "&longitude="& [longitude] &"&start_date=2025-01-13&end_date=" & [enddate] &  "&hourly=" & [hourly],  [Headers = [#"Accept-Encoding" = "gzip"]])) )
in
    #"Added Custom"

I expected the query below to refresh in the service as it did in the past but it appears an updateto  the service engine now invalidates this approach. Notice that I placed all parameters inside relativepath

let
    latitude = [latitude],
    longitude = [longitude],
    startdate = "2025-01-13",
    enddate = [enddate],
    hourly = [hourly],

    // Define the relative path with parameters
    relativePath = "/v1/archive?latitude=" & latitude & "&longitude=" & longitude & "&start_date=" & startdate & "&end_date=" & enddate & "&hourly=" & hourly,

    // Fetch data using Web.Contents with relative path and Accept-Encoding gzip
    Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com" & relativePath, [
        Headers = [#"Accept-Encoding" = "gzip"]
    ]))
in
    Source

The query below uses relative path for the base url and the parameters are inside Query = [...]. This will refresh in the service. You can confirm that by going to datasource settings. You shouldn't be seeing this warning:

let
    latitude = [latitude],
    longitude = [longitude],
    startdate = [startdate],
    enddate = [enddate],
    hourly = [hourly],

    // Define the relative path and query parameters
    relativePath = "v1/archive",

    // Fetch data using Web.Contents with RelativePath and Accept-Encoding gzip
    Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com", [
        RelativePath = relativePath,
        Query = [
            latitude = latitude,
            longitude = longitude,
            start_date = startdate,
            end_date = enddate,
            hourly = hourly
        ],
        Headers = [#"Accept-Encoding" = "gzip"]
    ]))
in
    Source

Here's the full working query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjXSMzVS0lEyNNYzMQTSRgZGproGhrqGxkgcI3MgpyQ1tyC1KLGktCg13ihXKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [latitude = _t, longitude = _t, startdate = _t, enddate = _t, hourly = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let
    latitude = [latitude],
    longitude = [longitude],
    startdate = [startdate],
    enddate = [enddate],
    hourly = [hourly],

    // Define the relative path and query parameters
    relativePath = "v1/archive",

    // Fetch data using Web.Contents with RelativePath and Accept-Encoding gzip
    Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com", [
        RelativePath = relativePath,
        Query = [
            latitude = latitude,
            longitude = longitude,
            start_date = startdate,
            end_date = enddate,
            hourly = hourly
        ],
        Headers = [#"Accept-Encoding" = "gzip"]
    ]))
in
    Source),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"latitude", "longitude", "generationtime_ms", "utc_offset_seconds", "timezone", "timezone_abbreviation", "elevation", "hourly_units", "hourly"}, {"latitude.1", "longitude.1", "generationtime_ms", "utc_offset_seconds", "timezone", "timezone_abbreviation", "elevation", "hourly_units", "hourly.1"})
in
    #"Expanded Custom"

[Griggs]

Am I correct in thinking that, applied to my query, it should look like this:

let
    todayDate = DateTime.Date(DateTime.LocalNow()),
    subtractedDate = Date.From((todayDate) - #duration(7, 0, 0, 0)),
    formattedDate = Date.ToText(todayDate, "yyyy-MM-dd"),
    formattedlastDate = Date.ToText(subtractedDate, "yyyy-MM-dd"),
    relativePath = "/api/1.4/reports/hours",
    Source = Json.Document(Web.Contents("https://www.findmyshift.co.uk", [
        RelativePath = relativePath,
        Query = [
            apiKey = "APIKEY",
            from = formattedlastDate,
            to = formattedDate
        ],
        Headers = [#"Accept-Encoding" = "gzip"]
    ])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teamId", "shiftId", "staffId", "facilityId", "firstName", "lastName", "payrollId", "day", "date", "start", "finish", "break", "breakStart", "breakFinish", "facility", "comments", "hourlyRate", "rateType", "rateCode", "rateMultiplier", "hours", "cost", "tax"}, {"teamId", "shiftId", "staffId", "facilityId", "firstName", "lastName", "payrollId", "day", "date", "start", "finish", "break", "breakStart", "breakFinish", "facility", "comments", "hourlyRate", "rateType", "rateCode", "rateMultiplier", "hours", "cost", "tax"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"teamId", type text}, {"shiftId", type text}, {"staffId", type text}, {"facilityId", type any}, {"firstName", type text}, {"lastName", type text}, {"payrollId", type text}, {"day", type text}, {"date", type date}, {"start", type datetime}, {"finish", type datetime}, {"break", type number}, {"breakStart", type any}, {"breakFinish", type any}, {"facility", type any}, {"comments", type text}, {"hourlyRate", Int64.Type}, {"rateType", type text}, {"rateCode", type any}, {"rateMultiplier", Int64.Type}, {"hours", type number}, {"cost", Int64.Type}, {"tax", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"teamId", "shiftId", "staffId", "facilityId", "payrollId", "break", "breakStart", "breakFinish", "facility", "hourlyRate", "rateCode", "rateMultiplier", "cost", "tax"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"firstName", Text.Trim, type text}, {"lastName", Text.Trim, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Trimmed Text",{"firstName", "lastName"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "FMS Name"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns")
in
    #"Removed Duplicates"

[danextian]

Hi @Griggs 

Looks about right to me. This is the full url to my sample.

https://archive-api.open-meteo.com/v1/archive?latitude=52.52&longitude=13.41&start_date=2025-01-13&e...

It is keyless so anyone can access. As the the encoding, you might want to check using the your browsers developer tool what option is used.

Note: note all encoding options are supported by Power Query thus I used gzip only.

[Griggs]

I tried yours and it loaded instantly. I tried mine and it was stuck on evaluating, exactly as it was in the old query. I left it for around 10 minutes without moving - as it trying to load no more that 800 rows of data, I would expect it to be pretty instantaneous. Again, the new query loaded correctly in Power Query and it loads in the web browser, just not when loading it to Power BI...

[danextian]

Have you tried deleting your cache or use the udpated query in a dataflow?

[Griggs]

I have cleared my cache a few times and just did so again - no luck.

I created a dataflow with the query and the same issue, it seems to get stuck loading the data. Not sure why I can see it in Power Query and not the others!