Unable to automatically refresh data in Power BI service due to retrieving paginated data via API

[jakubalbrecht]

Hello,

I am retrieving data via API. Unfortunatelly I am not able to get all the data with a single query as the count of the record exceeds the limit of the API.

That is why I am retriving ale the API pages using a Power Query function with the offset parameter (cf. "CRM Raynet API - events - get page data" function below). Getting data works in an excellent way in Power BI Desktop, but when I publish the report to Power BI service (online portal) and try to configure the plan for automatical data refresh, the following message appears: 

This dataset includes a dynamic data source. Since dynamic data sources are not updated in Power BI, this dataset will not be updated. More information: https://aka.ms/dynamic-data-sources.

The reason is that in the function the whole API connection string is not defined in a static way but contains a parameter to be specified during retriving all the data pages.

My question is: is there any way how to retrieve full data set from API divided into multiple data pages while keeping the ability to execute automaticaly data refresh according to the configuration plan in Power BI service (online portal)?

Thank you for your help!

My current coding:

function CRM Raynet API - events - get page data:

let
Source = (#"Raynet API offset" as text, #"Raynet API limit" as text) =>
let
Source = Json.Document(Web.Contents("https://app.raynet.cz/api/v2/" & "event/" & "?" & "limit=" & #"Raynet API limit" & "&" & "offset=" & #"Raynet API offset", [Headers=[#"X-Instance-Name"="...", Authorization="Basic ..."]])),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed table" = Table.Transpose(#"Converted to Table"),
#"Promoted headers" = Table.PromoteHeaders(#"Transposed table", [PromoteAllScalars=true]),
#"Changed type" = Table.TransformColumnTypes(#"Promoted headers",{{"success", type logical}, {"totalCount", Int64.Type}, {"data", type any}}),
#"Expanded data" = Table.ExpandListColumn(#"Changed type", "data"),
#"Extended data2" = Table.ExpandRecordColumn(#"Expanded data", "data", {"id", "title", "personal", "status", "priority", "category", "activity", "company", "person", "lead", "project", "businessCase", "offer", "salesOrder", "scheduledFrom", "scheduledTill", "completed", "description", "solution", "tags", "rowInfo.createdAt", "rowInfo.createdBy", "rowInfo.updatedAt", "rowInfo.updatedBy", "rowInfo.rowAccess", "rowInfo.rowState", "securityLevel", "_version", "_entityName", "customFields", "participants", "recurrence", "meetingPlace", "companyAddress"}, {"id", "title", "personal", "status", "priority", "category", "activity", "company", "person", "lead", "project", "businessCase", "offer", "salesOrder", "scheduledFrom", "scheduledTill", "completed", "description", "solution", "tags", "rowInfo.createdAt", "rowInfo.createdBy", "rowInfo.updatedAt", "rowInfo.updatedBy", "rowInfo.rowAccess", "rowInfo.rowState", "securityLevel", "_version", "_entityName", "customFields", "participants", "recurrence", "meetingPlace", "companyAddress"})
in
#"Extended data2"
in
Source

query CRM Raynet - API - events:

let
Source = #"CRM Raynet API - events - get page data" ("", ""),
#"Page List" = if Source = null then {null} else {1..Number.RoundUp(Source{0}[totalCount] / List.NonNullCount(Source[totalCount]))},
#"Converted to Table" = Table.FromList(#"Page List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Page", Int64.Type}}),
#"Add: Offset" = Table.AddColumn(#"Changed Type", "Offset", each ([Page]-1)*List.NonNullCount(Source[totalCount])),
#"Changed type" = Table.TransformColumnTypes(#"Add: Offset",{{"Offset", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed type", "Paginated data", each if [Page] <> null then #"CRM Raynet API - events - get page data"([Offset], "") else null),
#"Expanded Paginated data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Paginated data", {"success", "totalCount", "id", "title", "personal", "status", "priority", "category", "activity", "company", "person", "lead", "project", "businessCase", "offer", "salesOrder", "scheduledFrom", "scheduledTill", "completed", "description", "solution", "tags", "rowInfo.createdAt", "rowInfo.createdBy", "rowInfo.updatedAt", "rowInfo.updatedBy", "rowInfo.rowAccess", "rowInfo.rowState", "securityLevel", "_version", "_entityName", "customFields", "participants", "recurrence", "meetingPlace", "companyAddress"}, {"success", "totalCount", "id", "title", "personal", "status", "priority", "category", "activity", "company", "person", "lead", "project", "businessCase", "offer", "salesOrder", "scheduledFrom", "scheduledTill", "completed", "description", "solution", "tags", "rowInfo.createdAt", "rowInfo.createdBy", "rowInfo.updatedAt", "rowInfo.updatedBy", "rowInfo.rowAccess", "rowInfo.rowState", "securityLevel", "_version", "_entityName", "customFields", "participants", "recurrence", "meetingPlace", "companyAddress"}),
#"Add: Index" = Table.AddIndexColumn(#"Expanded Paginated data", "Index", 1, 1, Int64.Type),
#"Reordered columns" = Table.ReorderColumns(#"Add: Index",{"Index", "Page", "Offset", "success", "totalCount", "id", "title", "personal", "status", "priority", "category", "activity", "company", "person", "lead", "project", "businessCase", "offer", "salesOrder", "scheduledFrom", "scheduledTill", "completed", "description", "solution", "tags", "rowInfo.createdAt", "rowInfo.createdBy", "rowInfo.updatedAt", "rowInfo.updatedBy", "rowInfo.rowAccess", "rowInfo.rowState", "securityLevel", "_version", "_entityName", "customFields", "participants", "recurrence", "meetingPlace", "companyAddress"}),
#"Expanded category" = Table.ExpandRecordColumn(#"Reordered columns", "category", {"id", "value"}, {"category.id", "category.value"}),
#"Expanded company" = Table.ExpandRecordColumn(#"Expanded category", "company", {"id", "name"}, {"company.id", "company.name"}),
#"Expanded businessCase" = Table.ExpandRecordColumn(#"Expanded company", "businessCase", {"id", "code", "name"}, {"businessCase.id", "businessCase.code", "businessCase.name"}),
#"Extracted values" = Table.TransformColumns(#"Expanded businessCase", {"tags", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Expanded securityLevel" = Table.ExpandRecordColumn(#"Extracted values", "securityLevel", {"id", "name"}, {"securityLevel.id", "securityLevel.name"}),
#"Expanded customFields" = Table.ExpandRecordColumn(#"Expanded securityLevel", "customFields", {"Rozsah_6a9f9", "Typ_udalos_ed8bc", "Nazev_soub_57aa4"}, {"customFields.Rozsah_6a9f9", "customFields.Typ_udalos_ed8bc", "customFields.Nazev_soub_57aa4"}),
#"Expanded companyAddress" = Table.ExpandRecordColumn(#"Expanded customFields", "companyAddress", {"city", "country", "province", "street", "zipCode"}, {"companyAddress.city", "companyAddress.country", "companyAddress.province", "companyAddress.street", "companyAddress.zipCode"})
in
#"Expanded companyAddress"

[dr_kousek · Accepted solution]

You need to use RelativePath, then the error and automatic refresh works

@jakubalbrecht 

 apiUrl = "https://app.raynet.cz/api/v2/event/",
queryParameters = "?" & "limit=" & #"Raynet API limit" & "&" & "offset=" & #"Raynet API offset",
fullUrl = apiUrl,
response = Json.Document(Web.Contents(fullUrl, [
RelativePath=queryParameters,
Headers = [
#"X-Instance-Name" = "",
Authorization = "Basic ..."
]
])),
convertedToTable = Record.ToTable(response),
transposedTable = Table.Transpose(convertedToTable),
promotedHeaders = Table.PromoteHeaders(transposedTable, [PromoteAllScalars = true])

[jakubalbrecht]

Hello @dr_kousek ,

thank you for your answer. Yes, in the meantime I came to the same solution:

instead of 
Source = Json.Document(Web.Contents(#"Raynet API URL" & subject & "/" & detailId & "?" & "offset=" & offset, [Headers=[#"X-Instance-Name"="...", Authorization="Basic ..."]])),

I use

Source = Json.Document(Web.Contents(#"Raynet API URL", [RelativePath = subject & "/" & detailId & "?" & "offset=" & offset, Headers=[#"X-Instance-Name"="...", Authorization="Basic ..."]]))