Im facing an issue with Power BI report which is fetching data from an API.
There are 3 APIs.
first is for token generation, that token will be passed to other 2 APIs along with username and password, and then we will get the actual data.
Now to get data from both APIs limitations are -
1. the bearer token changes after 299 seconds which is approx. 5 min, so it should collect the new token and send for the next fetch till last page.
2. we can fetch only 1000 records per page.
The total pages have been given in an API pagination section. we can use it from there to break the loop.
I tried logic but its not loading all of the pages. It stops after 13th page. so need help to resolve that as total pages are 368 now.
below is the code Im using :
let
// Define parameters (Replace with Power BI Parameters)
Username = Text.From(username),
Password = Text.From(password),
GrantType = Text.From(grant_type),
// Function to get the Access Token (fetches a fresh token each time it's called)
GetAccessToken = () =>
let
body = "grant_type=" & GrantType & "&username=" & Username & "&password=" & Password,
response = Web.Contents("<AUTH_URL>",
[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)]),
jsonResponse = try Json.Document(response) otherwise null,
access_token = if jsonResponse <> null and Record.HasFields(jsonResponse, "access_token")
then jsonResponse[access_token]
else error "Failed to retrieve access token."
in
access_token,
// Function to fetch the first page and extract total pages dynamically
GetInitialData = () =>
let
Token = GetAccessToken(),
FirstResponse = Web.Contents("<BASE_API_URL>/customapi/executescript?scriptname=<SCRIPT_NAME>",
[
Headers = [
#"Authorization" = "Bearer " & Token,
#"Content-Type" = "application/json"
],
Content = Json.FromValue([rowsPerPage=1000, pageNumber=1])
]),
JsonResponse = try Json.Document(FirstResponse) otherwise null,
// Extract Data and Pagination Information
ExtractedData = if JsonResponse <> null and Record.HasFields(JsonResponse, "data") then JsonResponse[data] else {},
Pagination = if JsonResponse <> null and Record.HasFields(JsonResponse, "pagination") then JsonResponse[pagination] else null,
TotalPages = if Pagination <> null and Record.HasFields(Pagination, "totalPages") then Pagination[totalPages] else 1
in
[TotalPages = TotalPages, FirstPageData = ExtractedData],
// Get initial data to extract total pages
InitialResult = GetInitialData(),
TotalPages = InitialResult[TotalPages],
// Function to get the content for pagination
GetContent = (pageNumber as number) => Json.FromValue([rowsPerPage=1000, pageNumber=pageNumber]),
// Function to fetch data for each page with dynamic token refresh
FetchPageData = (pageNumber as number) =>
let
Token = GetAccessToken(), // Refresh token dynamically for each page
RawResponse = Web.Contents("<BASE_API_URL>/customapi/executescript?scriptname=<SCRIPT_NAME>",
[
Headers = [
#"Authorization" = "Bearer " & Token,
#"Content-Type" = "application/json"
],
Content = GetContent(pageNumber)
]),
JsonResponse = try Json.Document(RawResponse) otherwise null,
// Extract data safely
PageData = if JsonResponse <> null and Record.HasFields(JsonResponse, "data") then JsonResponse[data] else {}
in
PageData,
// Generate a list of pages dynamically based on extracted `TotalPages`
Pages = if TotalPages > 1 then List.Numbers(2, TotalPages - 1) else {},
// Fetch all remaining pages
OtherPagesData = List.Transform(Pages, each FetchPageData(_)),
// Combine first page data with all other pages
AllData = List.Combine(List.InsertRange(OtherPagesData, 0, {InitialResult[FirstPageData]})),
// Convert combined data to a table
DataTable = if List.IsEmpty(AllData) then #table({}, {}) else Table.FromList(AllData, Splitter.SplitByNothing(), {"Column1"}),
// Expand the columns (Replace with actual field names when needed)
#"Expanded Column1" = Table.ExpandRecordColumn(DataTable, "Column1",
{"Field1", "Field2", "Field3", "Field4", "Field5"})
in
#"Expanded Column1"
