Hello,
I have a Power BI report where I connected to data from ArcGIS Online using a public REST API endpoint. Because the REST API only returns 1,000 records per request, I wrote an M query (Power Query) that paginates through the API and combines all the data into a table. My query uses the Web.Contents function with the RelativePath and Query options (see code and API details below).
Everything works perfectly in Power BI Desktop, including the pagination and loading of all data. However, after publishing the report to Power BI Service (app.powerbi.com), I am unable to set up automatic data refresh. The Service tells me that a gateway is required, even though the API is public and requires no authentication.
My main questions:
Why does Power BI Service require an on-premises data gateway for refreshing data from a public REST API source, when no gateway is required for other online/cloud sources?
Is there any way to configure automatic data refresh for this REST API source in Power BI Service without using a data gateway?
Are there any alternatives or best practices for paginating and refreshing data from public APIs through Power BI Service?
Extra details:
let
// Velikost stránky
PageSize = 1000,
// Pevně dané ofsety: 0, 1000, 2000, ..., 499000
Offsets = List.Transform({0..499}, each _ * PageSize),
// Funkce pro načtení stránky s pevně danou URL přes RelativePath a Query
GetPage = (offset as number) =>
let
Response = Json.Document(
Web.Contents(
"https://gis.brno.cz",
[
RelativePath = "ags1/rest/services/Hosted/cyklo_meteo_merged/FeatureServer/0/query",
Query = [
where = "1=1",
outFields = "*",
returnGeometry = "true",
f = "json",
resultOffset = Text.From(offset),
resultRecordCount = Text.From(PageSize)
]
]
)
)
in
Response,
// Načtení všech stránek
Pages = List.Transform(Offsets, each GetPage(_)),
// Odstranění neplatných (null) stránek a ponechání jen těch s "features"
CleanPages = List.Select(Pages, each _ <> null and Record.HasFields(_, "features")),
// Sloučení všech záznamů "features" do jednoho seznamu
Features = List.Combine(List.Transform(CleanPages, each [features])),
// Převedení do tabulky a rozbalení
#"Converted to Table" = Table.FromList(Features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"attributes"}, {"attributes"}),
#"Expanded attributes" = Table.ExpandRecordColumn(#"Expanded Column1", "attributes", {
"globalid", "datetime_", "firstdirection_pedestrians", "seconddirection_name",
"seconddirection_cyclists", "precipitation", "pedestrianstotal", "cycliststotal",
"temperature", "unitid", "firstdirection_name", "objectid",
"seconddirection_pedestrians", "firstdirection_cyclists"
}, {
"globalid", "datetime_", "firstdirection_pedestrians", "seconddirection_name",
"seconddirection_cyclists", "precipitation", "pedestrianstotal", "cycliststotal",
"temperature", "unitid", "firstdirection_name", "objectid",
"seconddirection_pedestrians", "firstdirection_cyclists"
}),
// Změna datových typů
#"Changed Type" = Table.TransformColumnTypes(#"Expanded attributes", {
{"firstdirection_pedestrians", Int64.Type}, {"seconddirection_cyclists", Int64.Type},
{"precipitation", type number}, {"pedestrianstotal", Int64.Type},
{"cycliststotal", Int64.Type}, {"temperature", type number},
{"objectid", Int64.Type}, {"seconddirection_pedestrians", Int64.Type},
{"firstdirection_cyclists", Int64.Type}
}),
// Zpracování času
#"Added Custom" = Table.AddColumn(#"Changed Type", "datetime_parsed", each DateTime.FromText([datetime_], "en-US"), type datetime),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "datum", each DateTime.Date([datetime_parsed])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "hodina", each Time.Hour([datetime_parsed])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2", {{"datum", type date}, {"hodina", Int64.Type}})
in
#"Changed Type1"
