Forum Discussion
PowerBI Servie Issue with Dynamic Data Source, Pagination Issue
Hi there,
I have a issue fetiching an API via Power BI Service (Power BI Desktop works perfectly). I am using a pagination in the query and that makes it apperently dynamic and thus not compatiable with Power BI Service (only Power BI Desktop). When I delete the pagination from the code, the API fetch works both on Service and Desktop. So the Issue must be because of the pagination. How can I solve that, any ideas?
Here is the code with pagination that does not work on Power BI Service:
let
Source = let
// Define the date for the output
dateRange = "2018-01-01,3024-06-20",
// Function to get data from a specific page
GetPage = (page as number) as table =>
let
// Define the URL with the specified page
url = "https://api.<>document_date="& dateRange &"&page=" & Text.From(page),
// Define the headers, including Content-Type and Authorization with Bearer token
headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer <>"
],
// Send the request using Web.Contents, including the headers
response = Web.Contents(url, [
Headers = headers
]),
// Parse the JSON response
json = Json.Document(response),
// Extract the items from the JSON response
items = json[items],
// Transform the list of items to a list of records containing number and document_date
transformedItems = List.Transform(items, each [Customer_Name=_[customer_snapshot][company_name], Invoice_Number = _[number], Invoice_Date = _[document_date], Invoice_Amount = _[amount], Paid_Amount = _[paid_amount], Currency = _[currency], Payment_Date = _[paid_at]]),
// Convert the list of records to a table
resultTable = Table.FromRecords(transformedItems)
in
resultTable,
// Function to get all data using pagination
GetAllData = () =>
let
// Fetch the first page to get the total number of pages
firstPageResponse = Json.Document(Web.Contents("https://api.<>&document_date="& dateRange &"&page=1", [Headers = [
#"Content-Type" = "application/json",
Authorization = "Bearer <>"
]])),
totalPages = firstPageResponse[pages],
// Initialize a list to store all pages
allPages = List.Generate(
() => 1,
each _ <= totalPages,
each _ + 1,
each GetPage(_)
),
// Combine all pages into a single table
allData = Table.Combine(allPages)
in
allData,
Custom = GetAllData()
in
Custom,
#"Changed column type" = Table.TransformColumnTypes(Source, {{"Customer_Name", type text}, {"Invoice_Number", type text}, {"Invoice_Date", type text}, {"Invoice_Amount", Int64.Type}, {"Paid_Amount", Int64.Type}, {"Currency", type text}, {"Payment_Date", type text}})
in
#"Changed column type"
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
3 Replies
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
Perfect, thank you Idendlin the schedualed refreshes works now on power bi service!
Here is my new code:let
// Define the date range
dateRange = "2018-01-01,3024-06-20",// Define the base URL (common part of the URL)
baseUrl = "https://api.<>",// Function to get data from a specific page using RelativePath
GetPage = (page as number) as table =>
let
// Define the relative path with the specified page
relativePath = "<>" & dateRange & "&page=" & Text.From(page),
// Define the headers
headers = [
#"Content-Type" = "application/json",
Authorization = "<>"
],
// Send the request using Web.Contents with BaseUrl and RelativePath
response = Web.Contents(baseUrl, [
RelativePath = relativePath,
Headers = headers
]),
// Parse the JSON response
json = Json.Document(response),
// Extract the items from the JSON response
items = json[items],
// Transform the list of items to a list of records
transformedItems = List.Transform(items, each [
Customer_Name = _[customer_snapshot][company_name],
Invoice_Number = _[number],
Invoice_Date = _[document_date],
Invoice_Amount = _[amount],
Paid_Amount = _[paid_amount],
Currency = _[currency],
Payment_Date = _[paid_at]
]),
// Convert the list of records to a table
resultTable = Table.FromRecords(transformedItems)
in
resultTable,// Function to get all data using pagination
GetAllData = () =>
let
// Fetch the first page to get the total number of pages
firstPageResponse = Json.Document(Web.Contents(baseUrl, [
RelativePath = "<>" & dateRange & "&page=1",
Headers = [
#"Content-Type" = "application/json",
Authorization = "<>"
]
])),
totalPages = firstPageResponse[pages],// Initialize a list to store all pages
allPages = List.Generate(
() => 1,
each _ <= totalPages,
each _ + 1,
each GetPage(_)
),// Combine all pages into a single table
allData = Table.Combine(allPages)
in
allData,// Call the function to get all data
Custom = GetAllData(),// Change column types
#"Changed column type" = Table.TransformColumnTypes(Custom, {
{"Customer_Name", type text},
{"Invoice_Number", type text},
{"Invoice_Date", type text},
{"Invoice_Amount", Int64.Type},
{"Paid_Amount", Int64.Type},
{"Currency", type text},
{"Payment_Date", type text}
})
in
#"Changed column type"small comment: You are fetching the first page twice. Doesn't really matter as it likely comes from the cache the second time around.
