I have a report that is working fine in desktop and can be refreshed, but I am not able to refresh the dataset using Power BI Online as it states it has a dynamic data source. As I am only able to pull 8,500 record with each API call and there are usually about 50,000 records, I am using List.Generate and an offset for multiple API calls.
I have the below function that is used to call an API and has an offset that is dynamic to know how much data to pull. The function is below
FNGetSN
= (offset) =>
let
Source = Json.Document(Web.Contents("https://dummyURL.com/api/now/table/u_incident_user?sysparm_query=inc_sys_created_onONLast%203%20mont..." & Number.ToText ( offset ))),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded result" = Table.ExpandListColumn(#"Converted to Table", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"inc_u_call_type", "usr_email", "inc_u_customer", "inc_location", "inc_number", "inc_sys_created_on", "inc_close_notes", "inc_category", "inc_contact_type", "inc_state", "inc_priority", "inc_u_business_service", "inc_resolved_at", "inc_assignment_group", "inc_assigned_to", "inc_short_description", "inc_calendar_duration"}, {"result.inc_u_call_type", "result.usr_email", "result.inc_u_customer", "result.inc_location", "result.inc_number", "result.inc_sys_created_on", "result.inc_close_notes", "result.inc_category", "result.inc_contact_type", "result.inc_state", "result.inc_priority", "result.inc_u_business_service", "result.inc_resolved_at", "result.inc_assignment_group", "result.inc_assigned_to", "result.inc_short_description", "result.inc_calendar_duration"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded result1",{{"result.inc_u_call_type", type text}, {"result.usr_email", type text}, {"result.inc_u_customer", type text}, {"result.inc_location", type text}, {"result.inc_number", type text}, {"result.inc_sys_created_on", type datetime}, {"result.inc_close_notes", type text}, {"result.inc_category", type text}, {"result.inc_contact_type", type text}, {"result.inc_state", type text}, {"result.inc_priority", Int64.Type}, {"result.inc_u_business_service", type text}, {"result.inc_resolved_at", type datetime}, {"result.inc_assignment_group", type text}, {"result.inc_assigned_to", type text}, {"result.inc_short_description", type text}, {"result.inc_calendar_duration", type text}})
in
#"Changed Type"
And then I have a query that firstly uses an API to get a count of records and then uses that count to determine how many tables it needs to build. See below
SN (Query)
let
apiResponse = Json.Document(Web.Contents("https://dummyURL.com/api/now/stats/u_incident_user?sysparm_query=inc_sys_created_onONLast%203%20mont...")),
result = apiResponse[result],
stats = result[stats],
count = Number.FromText(stats[count]),
Source = List.Generate(
() => [offset = 8500, SNR = FNGetSN( 0 )],
each [offset] <= count+8500,
each [offset = [offset] + 8500, SNR = FNGetSN([offset])],
each [SNR]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"result.inc_u_call_type", "result.usr_email", "result.inc_u_customer", "result.inc_location", "result.inc_number", "result.inc_sys_created_on", "result.inc_close_notes", "result.inc_category", "result.inc_contact_type", "result.inc_state", "result.inc_priority", "result.inc_u_business_service", "result.inc_resolved_at", "result.inc_assignment_group", "result.inc_assigned_to", "result.inc_short_description", "result.inc_calendar_duration"}, {"result.inc_u_call_type", "result.usr_email", "result.inc_u_customer", "result.inc_location", "result.inc_number", "result.inc_sys_created_on", "result.inc_close_notes", "result.inc_category", "result.inc_contact_type", "result.inc_state", "result.inc_priority", "result.inc_u_business_service", "result.inc_resolved_at", "result.inc_assignment_group", "result.inc_assigned_to", "result.inc_short_description", "result.inc_calendar_duration"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"result.inc_sys_created_on", type datetime}}),
in
#"Changed Type"
Is there any way around this, as it works exactly as I want, but I just need to be able to refresh the data in Power BI Online?
I would really appreciate any guidance and support on this.
Thanks
