This guide provides example Excel (Advanced Power Query) and Power BI query templates for specific ALIS API (v2) endpoints. You can copy the script from these templates directly into the Advanced Editor of your respective tool.
For more information on ALIS API exports, refer to this guide: ALIS API Reference Guide.
Why do I need different query templates for Excel and Power BI?
Even though both Advanced Power Query (Excel) and Power BI tools look similar and use the same "Power Query" engine, they require different script structures (code) to function correctly:
The Excel Script is optimized for local performance on your desktop.
The Power BI Script is specifically modified to work in the cloud, allowing you to use Scheduled Refreshes so your dashboard updates automatically overnight.
Excel (Advanced Power Query) Templates
The following templates are designed for local data processing in Excel. They use a simple looping logic suitable for desktop performance and manual analysis.
Recorded Care
let
SourceUrl = "https://api.alisonline.com/v2/export/care/recordedCare?careStartDate=10%2F27%2F2025&careEndDate=11%2F2%2F2025&pageNumber=1&pageSize=10000",
RequestHeaders = [
#"Content-Type" = "application/json"
],
GetPage = (url as text) as record =>
let
RequestHeaders = [
#"Content-Type" = "application/json"
],
WebContent = Web.Contents(url, [Headers = RequestHeaders]),
JsonContent = Json.Document(WebContent),
Result = JsonContent
in
Result,
GetAllPages = (initialUrl as text) as list =>
let
PageList = List.Generate(
() => [
CurrentUrl = initialUrl,
Page = GetPage(initialUrl),
HasMore = true // Flag to control the loop
],
each [HasMore],
each let
nextUrl = [Page][nextPageUrl],
nextPage = if nextUrl <> null and nextUrl <> "" then GetPage(nextUrl) else null
in
[
CurrentUrl = nextUrl,
Page = nextPage,
HasMore = nextPage <> null
],
each [Page]
)
in
PageList,
AllPagesData = GetAllPages(SourceUrl),
ListOfItemLists = List.Transform(AllPagesData, each [items]),
CombinedItemsList = List.Combine(ListOfItemLists),
ResultTable = Table.FromList(CombinedItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedTable = Table.ExpandRecordColumn(
ResultTable, "Column1",
Record.FieldNames(CombinedItemsList{0})
)
in
#"ExpandedTable"Power BI Templates
The following templates are strictly formatted to support Power BI. They use static base URLs and "Relative Paths" to ensure that Scheduled Refreshes work without errors once published.
Recorded Care
let
// 1. Define the static root URL (required for scheduled refresh)
RootUrl = "https://api.alisonline.com",
// 2. Define the static base path and initial parameters
// The [PATH] part should be replaced with the actual static path component.
StaticPath = "/v2/export/care/recordedCare",
InitialQuery = [
pageNumber = "1",
pageSize = "10000"
],
// Helper function to build the query parameters for a given page number
GetQueryParams = (page as number) as record => [
pageNumber = Text.From(page),
pageSize = "10000"
],
// 3. Rework GetPage to use RelativePath and Query options
GetPage = (page as number) as record =>
let
QueryParams = GetQueryParams(page),
// Use Web.Contents with RootUrl, RelativePath, and Query
WebContent = Web.Contents(
RootUrl,
[
RelativePath = StaticPath,
Query = QueryParams, // Power BI can now 'see' the static RootUrl
Headers = [#"Content-Type" = "application/json"]
]
),
JsonContent = Json.Document(WebContent),
Result = JsonContent
in
Result,
// 4. Update the logic to use List.Generate with a page counter
GetAllPages = () as list =>
let
PageList = List.Generate(
() => [
PageNumber = 1,
Page = GetPage(1),
HasMore = true
],
// Condition to check if there is data on the page
each List.Count([Page][items]) > 0, // Assuming 'items' contains the data
each let
nextNumber = [PageNumber] + 1,
nextPage = GetPage(nextNumber)
in
[
PageNumber = nextNumber,
Page = nextPage,
HasMore = List.Count(nextPage[items]) > 0
],
each [Page]
)
in
PageList,
// 5. Rest of the transformation remains similar
AllPagesData = GetAllPages(),
ListOfItemLists = List.Transform(AllPagesData, each [items]),
CombinedItemsList = List.Combine(ListOfItemLists),
// Assuming the structure is consistent: records in a list
// Use the record fields from the first item to define the table columns
SampleItem = CombinedItemsList{0},
ResultTable = Table.FromList(CombinedItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedTable = Table.ExpandRecordColumn(
ResultTable, "Column1",
Record.FieldNames(SampleItem)
)
in
ExpandedTableOrder Administration
let
// SOURCE URL
SourceUrl = "https://api.alisonline.com/v2/export/clinical/orderAdministration?startDate=[START DATE]&endDate=[END DATE]&pageNumber=1&pageSize=10000",
// Can use the Source URL below if using the community ID parameter
// SourceUrl = "https://api.alisonline.com/v2/export/clinical/orderAdministration?communityId=[COMMUNITY ID]startDate=[START DATE]&endDate=[END DATE]&pageNumber=1&pageSize=10000",
// Request headers
RequestHeaders = [
#"Content-Type" = "application/json"
],
// Function to get a page
GetPage = (url as text) as record =>
let
uri = Uri.Parts(url),
relativeUrl = uri[Path],
queryOptions = Uri.Parts(url)[Query],
WebContent = Web.Contents(
baseUrl,
[
Headers = RequestHeaders,
RelativePath = relativeUrl,
Query = queryOptions
]
),
JsonContent = Json.Document(WebContent)
in
JsonContent,
GetAllPages = (initialUrl as text) as list =>
let
PageList = List.Generate(
() => [
CurrentUrl = initialUrl,
Page = GetPage(initialUrl),
HasMore = true // Flag to control the loop
],
each [HasMore],
each let
nextUrl = [Page][nextPageUrl],
nextPage = if nextUrl <> null and nextUrl <> "" then GetPage(nextUrl) else null
in
[
CurrentUrl = nextUrl,
Page = nextPage,
HasMore = nextPage <> null
],
each [Page]
)
in
PageList,
// Fetch Data
AllPagesData = GetAllPages(SourceUrl),
ListOfItemLists = List.Transform(AllPagesData, each [items]),
CombinedItemsList = List.Combine(ListOfItemLists),
ResultTable = Table.FromList(CombinedItemsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedTable = Table.ExpandRecordColumn(
ResultTable, "Column1",
Record.FieldNames(CombinedItemsList{0})
)
in
#"ExpandedTable"
Comments
0 comments
Article is closed for comments.